1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    is_int,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot._typing import E, Lit
  40    from sqlglot.dialects.dialect import DialectType
  41
  42
  43class _Expression(type):
  44    def __new__(cls, clsname, bases, attrs):
  45        klass = super().__new__(cls, clsname, bases, attrs)
  46
  47        # When an Expression class is created, its key is automatically set to be
  48        # the lowercase version of the class' name.
  49        klass.key = clsname.lower()
  50
  51        # This is so that docstrings are not inherited in pdoc
  52        klass.__doc__ = klass.__doc__ or ""
  53
  54        return klass
  55
  56
  57SQLGLOT_META = "sqlglot.meta"
  58TABLE_PARTS = ("this", "db", "catalog")
  59
  60
  61class Expression(metaclass=_Expression):
  62    """
  63    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  64    context, such as its child expressions, their names (arg keys), and whether a given child expression
  65    is optional or not.
  66
  67    Attributes:
  68        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  69            and representing expressions as strings.
  70        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  71            arg keys to booleans that indicate whether the corresponding args are optional.
  72        parent: a reference to the parent expression (or None, in case of root expressions).
  73        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  74            uses to refer to it.
  75        comments: a list of comments that are associated with a given expression. This is used in
  76            order to preserve comments when transpiling SQL code.
  77        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  78            optimizer, in order to enable some transformations that require type information.
  79        meta: a dictionary that can be used to store useful metadata for a given expression.
  80
  81    Example:
  82        >>> class Foo(Expression):
  83        ...     arg_types = {"this": True, "expression": False}
  84
  85        The above definition informs us that Foo is an Expression that requires an argument called
  86        "this" and may also optionally receive an argument called "expression".
  87
  88    Args:
  89        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  90    """
  91
  92    key = "expression"
  93    arg_types = {"this": True}
  94    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  95
  96    def __init__(self, **args: t.Any):
  97        self.args: t.Dict[str, t.Any] = args
  98        self.parent: t.Optional[Expression] = None
  99        self.arg_key: t.Optional[str] = None
 100        self.comments: t.Optional[t.List[str]] = None
 101        self._type: t.Optional[DataType] = None
 102        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 103        self._hash: t.Optional[int] = None
 104
 105        for arg_key, value in self.args.items():
 106            self._set_parent(arg_key, value)
 107
 108    def __eq__(self, other) -> bool:
 109        return type(self) is type(other) and hash(self) == hash(other)
 110
 111    @property
 112    def hashable_args(self) -> t.Any:
 113        return frozenset(
 114            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 115            for k, v in self.args.items()
 116            if not (v is None or v is False or (type(v) is list and not v))
 117        )
 118
 119    def __hash__(self) -> int:
 120        if self._hash is not None:
 121            return self._hash
 122
 123        return hash((self.__class__, self.hashable_args))
 124
 125    @property
 126    def this(self) -> t.Any:
 127        """
 128        Retrieves the argument with key "this".
 129        """
 130        return self.args.get("this")
 131
 132    @property
 133    def expression(self) -> t.Any:
 134        """
 135        Retrieves the argument with key "expression".
 136        """
 137        return self.args.get("expression")
 138
 139    @property
 140    def expressions(self) -> t.List[t.Any]:
 141        """
 142        Retrieves the argument with key "expressions".
 143        """
 144        return self.args.get("expressions") or []
 145
 146    def text(self, key) -> str:
 147        """
 148        Returns a textual representation of the argument corresponding to "key". This can only be used
 149        for args that are strings or leaf Expression instances, such as identifiers and literals.
 150        """
 151        field = self.args.get(key)
 152        if isinstance(field, str):
 153            return field
 154        if isinstance(field, (Identifier, Literal, Var)):
 155            return field.this
 156        if isinstance(field, (Star, Null)):
 157            return field.name
 158        return ""
 159
 160    @property
 161    def is_string(self) -> bool:
 162        """
 163        Checks whether a Literal expression is a string.
 164        """
 165        return isinstance(self, Literal) and self.args["is_string"]
 166
 167    @property
 168    def is_number(self) -> bool:
 169        """
 170        Checks whether a Literal expression is a number.
 171        """
 172        return isinstance(self, Literal) and not self.args["is_string"]
 173
 174    @property
 175    def is_int(self) -> bool:
 176        """
 177        Checks whether a Literal expression is an integer.
 178        """
 179        return self.is_number and is_int(self.name)
 180
 181    @property
 182    def is_star(self) -> bool:
 183        """Checks whether an expression is a star."""
 184        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 185
 186    @property
 187    def alias(self) -> str:
 188        """
 189        Returns the alias of the expression, or an empty string if it's not aliased.
 190        """
 191        if isinstance(self.args.get("alias"), TableAlias):
 192            return self.args["alias"].name
 193        return self.text("alias")
 194
 195    @property
 196    def alias_column_names(self) -> t.List[str]:
 197        table_alias = self.args.get("alias")
 198        if not table_alias:
 199            return []
 200        return [c.name for c in table_alias.args.get("columns") or []]
 201
 202    @property
 203    def name(self) -> str:
 204        return self.text("this")
 205
 206    @property
 207    def alias_or_name(self) -> str:
 208        return self.alias or self.name
 209
 210    @property
 211    def output_name(self) -> str:
 212        """
 213        Name of the output column if this expression is a selection.
 214
 215        If the Expression has no output name, an empty string is returned.
 216
 217        Example:
 218            >>> from sqlglot import parse_one
 219            >>> parse_one("SELECT a").expressions[0].output_name
 220            'a'
 221            >>> parse_one("SELECT b AS c").expressions[0].output_name
 222            'c'
 223            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 224            ''
 225        """
 226        return ""
 227
 228    @property
 229    def type(self) -> t.Optional[DataType]:
 230        return self._type
 231
 232    @type.setter
 233    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 234        if dtype and not isinstance(dtype, DataType):
 235            dtype = DataType.build(dtype)
 236        self._type = dtype  # type: ignore
 237
 238    def is_type(self, *dtypes) -> bool:
 239        return self.type is not None and self.type.is_type(*dtypes)
 240
 241    def is_leaf(self) -> bool:
 242        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 243
 244    @property
 245    def meta(self) -> t.Dict[str, t.Any]:
 246        if self._meta is None:
 247            self._meta = {}
 248        return self._meta
 249
 250    def __deepcopy__(self, memo):
 251        root = self.__class__()
 252        stack = [(self, root)]
 253
 254        while stack:
 255            node, copy = stack.pop()
 256
 257            if node.comments is not None:
 258                copy.comments = deepcopy(node.comments)
 259            if node._type is not None:
 260                copy._type = deepcopy(node._type)
 261            if node._meta is not None:
 262                copy._meta = deepcopy(node._meta)
 263            if node._hash is not None:
 264                copy._hash = node._hash
 265
 266            for k, vs in node.args.items():
 267                if hasattr(vs, "parent"):
 268                    stack.append((vs, vs.__class__()))
 269                    copy.set(k, stack[-1][-1])
 270                elif type(vs) is list:
 271                    copy.args[k] = []
 272
 273                    for v in vs:
 274                        if hasattr(v, "parent"):
 275                            stack.append((v, v.__class__()))
 276                            copy.append(k, stack[-1][-1])
 277                        else:
 278                            copy.append(k, v)
 279                else:
 280                    copy.set(k, vs)
 281
 282        return root
 283
 284    def copy(self):
 285        """
 286        Returns a deep copy of the expression.
 287        """
 288        new = deepcopy(self)
 289        new.parent = self.parent
 290        return new
 291
 292    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 293        if self.comments is None:
 294            self.comments = []
 295        if comments:
 296            for comment in comments:
 297                _, *meta = comment.split(SQLGLOT_META)
 298                if meta:
 299                    for kv in "".join(meta).split(","):
 300                        k, *v = kv.split("=")
 301                        value = v[0].strip() if v else True
 302                        self.meta[k.strip()] = value
 303                self.comments.append(comment)
 304
 305    def append(self, arg_key: str, value: t.Any) -> None:
 306        """
 307        Appends value to arg_key if it's a list or sets it as a new list.
 308
 309        Args:
 310            arg_key (str): name of the list expression arg
 311            value (Any): value to append to the list
 312        """
 313        if type(self.args.get(arg_key)) is not list:
 314            self.args[arg_key] = []
 315        self.args[arg_key].append(value)
 316        self._set_parent(arg_key, value)
 317
 318    def set(self, arg_key: str, value: t.Any) -> None:
 319        """
 320        Sets arg_key to value.
 321
 322        Args:
 323            arg_key: name of the expression arg.
 324            value: value to set the arg to.
 325        """
 326        if value is None:
 327            self.args.pop(arg_key, None)
 328            return
 329
 330        self.args[arg_key] = value
 331        self._set_parent(arg_key, value)
 332
 333    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 334        if hasattr(value, "parent"):
 335            value.parent = self
 336            value.arg_key = arg_key
 337        elif type(value) is list:
 338            for v in value:
 339                if hasattr(v, "parent"):
 340                    v.parent = self
 341                    v.arg_key = arg_key
 342
 343    @property
 344    def depth(self) -> int:
 345        """
 346        Returns the depth of this tree.
 347        """
 348        if self.parent:
 349            return self.parent.depth + 1
 350        return 0
 351
 352    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 353        """Yields the key and expression for all arguments, exploding list args."""
 354        for k, vs in self.args.items():
 355            if type(vs) is list:
 356                for v in vs:
 357                    if hasattr(v, "parent"):
 358                        yield k, v
 359            else:
 360                if hasattr(vs, "parent"):
 361                    yield k, vs
 362
 363    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 364        """
 365        Returns the first node in this tree which matches at least one of
 366        the specified types.
 367
 368        Args:
 369            expression_types: the expression type(s) to match.
 370            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 371
 372        Returns:
 373            The node which matches the criteria or None if no such node was found.
 374        """
 375        return next(self.find_all(*expression_types, bfs=bfs), None)
 376
 377    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 378        """
 379        Returns a generator object which visits all nodes in this tree and only
 380        yields those that match at least one of the specified expression types.
 381
 382        Args:
 383            expression_types: the expression type(s) to match.
 384            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 385
 386        Returns:
 387            The generator object.
 388        """
 389        for expression, *_ in self.walk(bfs=bfs):
 390            if isinstance(expression, expression_types):
 391                yield expression
 392
 393    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 394        """
 395        Returns a nearest parent matching expression_types.
 396
 397        Args:
 398            expression_types: the expression type(s) to match.
 399
 400        Returns:
 401            The parent node.
 402        """
 403        ancestor = self.parent
 404        while ancestor and not isinstance(ancestor, expression_types):
 405            ancestor = ancestor.parent
 406        return ancestor  # type: ignore
 407
 408    @property
 409    def parent_select(self) -> t.Optional[Select]:
 410        """
 411        Returns the parent select statement.
 412        """
 413        return self.find_ancestor(Select)
 414
 415    @property
 416    def same_parent(self) -> bool:
 417        """Returns if the parent is the same class as itself."""
 418        return type(self.parent) is self.__class__
 419
 420    def root(self) -> Expression:
 421        """
 422        Returns the root expression of this tree.
 423        """
 424        expression = self
 425        while expression.parent:
 426            expression = expression.parent
 427        return expression
 428
 429    def walk(self, bfs=True, prune=None):
 430        """
 431        Returns a generator object which visits all nodes in this tree.
 432
 433        Args:
 434            bfs (bool): if set to True the BFS traversal order will be applied,
 435                otherwise the DFS traversal will be used instead.
 436            prune ((node, parent, arg_key) -> bool): callable that returns True if
 437                the generator should stop traversing this branch of the tree.
 438
 439        Returns:
 440            the generator object.
 441        """
 442        if bfs:
 443            yield from self.bfs(prune=prune)
 444        else:
 445            yield from self.dfs(prune=prune)
 446
 447    def dfs(self, parent=None, key=None, prune=None):
 448        """
 449        Returns a generator object which visits all nodes in this tree in
 450        the DFS (Depth-first) order.
 451
 452        Returns:
 453            The generator object.
 454        """
 455        parent = parent or self.parent
 456        yield self, parent, key
 457        if prune and prune(self, parent, key):
 458            return
 459
 460        for k, v in self.iter_expressions():
 461            yield from v.dfs(self, k, prune)
 462
 463    def bfs(self, prune=None):
 464        """
 465        Returns a generator object which visits all nodes in this tree in
 466        the BFS (Breadth-first) order.
 467
 468        Returns:
 469            The generator object.
 470        """
 471        queue = deque([(self, self.parent, None)])
 472
 473        while queue:
 474            item, parent, key = queue.popleft()
 475
 476            yield item, parent, key
 477            if prune and prune(item, parent, key):
 478                continue
 479
 480            for k, v in item.iter_expressions():
 481                queue.append((v, item, k))
 482
 483    def unnest(self):
 484        """
 485        Returns the first non parenthesis child or self.
 486        """
 487        expression = self
 488        while type(expression) is Paren:
 489            expression = expression.this
 490        return expression
 491
 492    def unalias(self):
 493        """
 494        Returns the inner expression if this is an Alias.
 495        """
 496        if isinstance(self, Alias):
 497            return self.this
 498        return self
 499
 500    def unnest_operands(self):
 501        """
 502        Returns unnested operands as a tuple.
 503        """
 504        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 505
 506    def flatten(self, unnest=True):
 507        """
 508        Returns a generator which yields child nodes whose parents are the same class.
 509
 510        A AND B AND C -> [A, B, C]
 511        """
 512        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and type(n) is not self.__class__):
 513            if type(node) is not self.__class__:
 514                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 515
 516    def __str__(self) -> str:
 517        return self.sql()
 518
 519    def __repr__(self) -> str:
 520        return _to_s(self)
 521
 522    def to_s(self) -> str:
 523        """
 524        Same as __repr__, but includes additional information which can be useful
 525        for debugging, like empty or missing args and the AST nodes' object IDs.
 526        """
 527        return _to_s(self, verbose=True)
 528
 529    def sql(self, dialect: DialectType = None, **opts) -> str:
 530        """
 531        Returns SQL string representation of this tree.
 532
 533        Args:
 534            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 535            opts: other `sqlglot.generator.Generator` options.
 536
 537        Returns:
 538            The SQL string.
 539        """
 540        from sqlglot.dialects import Dialect
 541
 542        return Dialect.get_or_raise(dialect).generate(self, **opts)
 543
 544    def transform(self, fun, *args, copy=True, **kwargs):
 545        """
 546        Recursively visits all tree nodes (excluding already transformed ones)
 547        and applies the given transformation function to each node.
 548
 549        Args:
 550            fun (function): a function which takes a node as an argument and returns a
 551                new transformed node or the same node without modifications. If the function
 552                returns None, then the corresponding node will be removed from the syntax tree.
 553            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 554                modified in place.
 555
 556        Returns:
 557            The transformed tree.
 558        """
 559        node = self.copy() if copy else self
 560        new_node = fun(node, *args, **kwargs)
 561
 562        if new_node is None or not isinstance(new_node, Expression):
 563            return new_node
 564        if new_node is not node:
 565            new_node.parent = node.parent
 566            return new_node
 567
 568        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 569        return new_node
 570
 571    @t.overload
 572    def replace(self, expression: E) -> E: ...
 573
 574    @t.overload
 575    def replace(self, expression: None) -> None: ...
 576
 577    def replace(self, expression):
 578        """
 579        Swap out this expression with a new expression.
 580
 581        For example::
 582
 583            >>> tree = Select().select("x").from_("tbl")
 584            >>> tree.find(Column).replace(column("y"))
 585            Column(
 586              this=Identifier(this=y, quoted=False))
 587            >>> tree.sql()
 588            'SELECT y FROM tbl'
 589
 590        Args:
 591            expression: new node
 592
 593        Returns:
 594            The new expression or expressions.
 595        """
 596        if not self.parent:
 597            return expression
 598
 599        parent = self.parent
 600        self.parent = None
 601
 602        replace_children(parent, lambda child: expression if child is self else child)
 603        return expression
 604
 605    def pop(self: E) -> E:
 606        """
 607        Remove this expression from its AST.
 608
 609        Returns:
 610            The popped expression.
 611        """
 612        self.replace(None)
 613        return self
 614
 615    def assert_is(self, type_: t.Type[E]) -> E:
 616        """
 617        Assert that this `Expression` is an instance of `type_`.
 618
 619        If it is NOT an instance of `type_`, this raises an assertion error.
 620        Otherwise, this returns this expression.
 621
 622        Examples:
 623            This is useful for type security in chained expressions:
 624
 625            >>> import sqlglot
 626            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 627            'SELECT x, z FROM y'
 628        """
 629        if not isinstance(self, type_):
 630            raise AssertionError(f"{self} is not {type_}.")
 631        return self
 632
 633    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 634        """
 635        Checks if this expression is valid (e.g. all mandatory args are set).
 636
 637        Args:
 638            args: a sequence of values that were used to instantiate a Func expression. This is used
 639                to check that the provided arguments don't exceed the function argument limit.
 640
 641        Returns:
 642            A list of error messages for all possible errors that were found.
 643        """
 644        errors: t.List[str] = []
 645
 646        for k in self.args:
 647            if k not in self.arg_types:
 648                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 649        for k, mandatory in self.arg_types.items():
 650            v = self.args.get(k)
 651            if mandatory and (v is None or (isinstance(v, list) and not v)):
 652                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 653
 654        if (
 655            args
 656            and isinstance(self, Func)
 657            and len(args) > len(self.arg_types)
 658            and not self.is_var_len_args
 659        ):
 660            errors.append(
 661                f"The number of provided arguments ({len(args)}) is greater than "
 662                f"the maximum number of supported arguments ({len(self.arg_types)})"
 663            )
 664
 665        return errors
 666
 667    def dump(self):
 668        """
 669        Dump this Expression to a JSON-serializable dict.
 670        """
 671        from sqlglot.serde import dump
 672
 673        return dump(self)
 674
 675    @classmethod
 676    def load(cls, obj):
 677        """
 678        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 679        """
 680        from sqlglot.serde import load
 681
 682        return load(obj)
 683
 684    def and_(
 685        self,
 686        *expressions: t.Optional[ExpOrStr],
 687        dialect: DialectType = None,
 688        copy: bool = True,
 689        **opts,
 690    ) -> Condition:
 691        """
 692        AND this condition with one or multiple expressions.
 693
 694        Example:
 695            >>> condition("x=1").and_("y=1").sql()
 696            'x = 1 AND y = 1'
 697
 698        Args:
 699            *expressions: the SQL code strings to parse.
 700                If an `Expression` instance is passed, it will be used as-is.
 701            dialect: the dialect used to parse the input expression.
 702            copy: whether to copy the involved expressions (only applies to Expressions).
 703            opts: other options to use to parse the input expressions.
 704
 705        Returns:
 706            The new And condition.
 707        """
 708        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 709
 710    def or_(
 711        self,
 712        *expressions: t.Optional[ExpOrStr],
 713        dialect: DialectType = None,
 714        copy: bool = True,
 715        **opts,
 716    ) -> Condition:
 717        """
 718        OR this condition with one or multiple expressions.
 719
 720        Example:
 721            >>> condition("x=1").or_("y=1").sql()
 722            'x = 1 OR y = 1'
 723
 724        Args:
 725            *expressions: the SQL code strings to parse.
 726                If an `Expression` instance is passed, it will be used as-is.
 727            dialect: the dialect used to parse the input expression.
 728            copy: whether to copy the involved expressions (only applies to Expressions).
 729            opts: other options to use to parse the input expressions.
 730
 731        Returns:
 732            The new Or condition.
 733        """
 734        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 735
 736    def not_(self, copy: bool = True):
 737        """
 738        Wrap this condition with NOT.
 739
 740        Example:
 741            >>> condition("x=1").not_().sql()
 742            'NOT x = 1'
 743
 744        Args:
 745            copy: whether to copy this object.
 746
 747        Returns:
 748            The new Not instance.
 749        """
 750        return not_(self, copy=copy)
 751
 752    def as_(
 753        self,
 754        alias: str | Identifier,
 755        quoted: t.Optional[bool] = None,
 756        dialect: DialectType = None,
 757        copy: bool = True,
 758        **opts,
 759    ) -> Alias:
 760        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 761
 762    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 763        this = self.copy()
 764        other = convert(other, copy=True)
 765        if not isinstance(this, klass) and not isinstance(other, klass):
 766            this = _wrap(this, Binary)
 767            other = _wrap(other, Binary)
 768        if reverse:
 769            return klass(this=other, expression=this)
 770        return klass(this=this, expression=other)
 771
 772    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 773        return Bracket(
 774            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 775        )
 776
 777    def __iter__(self) -> t.Iterator:
 778        if "expressions" in self.arg_types:
 779            return iter(self.args.get("expressions") or [])
 780        # We define this because __getitem__ converts Expression into an iterable, which is
 781        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 782        # See: https://peps.python.org/pep-0234/
 783        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 784
 785    def isin(
 786        self,
 787        *expressions: t.Any,
 788        query: t.Optional[ExpOrStr] = None,
 789        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 790        copy: bool = True,
 791        **opts,
 792    ) -> In:
 793        return In(
 794            this=maybe_copy(self, copy),
 795            expressions=[convert(e, copy=copy) for e in expressions],
 796            query=maybe_parse(query, copy=copy, **opts) if query else None,
 797            unnest=(
 798                Unnest(
 799                    expressions=[
 800                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 801                        for e in ensure_list(unnest)
 802                    ]
 803                )
 804                if unnest
 805                else None
 806            ),
 807        )
 808
 809    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 810        return Between(
 811            this=maybe_copy(self, copy),
 812            low=convert(low, copy=copy, **opts),
 813            high=convert(high, copy=copy, **opts),
 814        )
 815
 816    def is_(self, other: ExpOrStr) -> Is:
 817        return self._binop(Is, other)
 818
 819    def like(self, other: ExpOrStr) -> Like:
 820        return self._binop(Like, other)
 821
 822    def ilike(self, other: ExpOrStr) -> ILike:
 823        return self._binop(ILike, other)
 824
 825    def eq(self, other: t.Any) -> EQ:
 826        return self._binop(EQ, other)
 827
 828    def neq(self, other: t.Any) -> NEQ:
 829        return self._binop(NEQ, other)
 830
 831    def rlike(self, other: ExpOrStr) -> RegexpLike:
 832        return self._binop(RegexpLike, other)
 833
 834    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 835        div = self._binop(Div, other)
 836        div.args["typed"] = typed
 837        div.args["safe"] = safe
 838        return div
 839
 840    def desc(self, nulls_first: bool = False) -> Ordered:
 841        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 842
 843    def __lt__(self, other: t.Any) -> LT:
 844        return self._binop(LT, other)
 845
 846    def __le__(self, other: t.Any) -> LTE:
 847        return self._binop(LTE, other)
 848
 849    def __gt__(self, other: t.Any) -> GT:
 850        return self._binop(GT, other)
 851
 852    def __ge__(self, other: t.Any) -> GTE:
 853        return self._binop(GTE, other)
 854
 855    def __add__(self, other: t.Any) -> Add:
 856        return self._binop(Add, other)
 857
 858    def __radd__(self, other: t.Any) -> Add:
 859        return self._binop(Add, other, reverse=True)
 860
 861    def __sub__(self, other: t.Any) -> Sub:
 862        return self._binop(Sub, other)
 863
 864    def __rsub__(self, other: t.Any) -> Sub:
 865        return self._binop(Sub, other, reverse=True)
 866
 867    def __mul__(self, other: t.Any) -> Mul:
 868        return self._binop(Mul, other)
 869
 870    def __rmul__(self, other: t.Any) -> Mul:
 871        return self._binop(Mul, other, reverse=True)
 872
 873    def __truediv__(self, other: t.Any) -> Div:
 874        return self._binop(Div, other)
 875
 876    def __rtruediv__(self, other: t.Any) -> Div:
 877        return self._binop(Div, other, reverse=True)
 878
 879    def __floordiv__(self, other: t.Any) -> IntDiv:
 880        return self._binop(IntDiv, other)
 881
 882    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 883        return self._binop(IntDiv, other, reverse=True)
 884
 885    def __mod__(self, other: t.Any) -> Mod:
 886        return self._binop(Mod, other)
 887
 888    def __rmod__(self, other: t.Any) -> Mod:
 889        return self._binop(Mod, other, reverse=True)
 890
 891    def __pow__(self, other: t.Any) -> Pow:
 892        return self._binop(Pow, other)
 893
 894    def __rpow__(self, other: t.Any) -> Pow:
 895        return self._binop(Pow, other, reverse=True)
 896
 897    def __and__(self, other: t.Any) -> And:
 898        return self._binop(And, other)
 899
 900    def __rand__(self, other: t.Any) -> And:
 901        return self._binop(And, other, reverse=True)
 902
 903    def __or__(self, other: t.Any) -> Or:
 904        return self._binop(Or, other)
 905
 906    def __ror__(self, other: t.Any) -> Or:
 907        return self._binop(Or, other, reverse=True)
 908
 909    def __neg__(self) -> Neg:
 910        return Neg(this=_wrap(self.copy(), Binary))
 911
 912    def __invert__(self) -> Not:
 913        return not_(self.copy())
 914
 915
 916IntoType = t.Union[
 917    str,
 918    t.Type[Expression],
 919    t.Collection[t.Union[str, t.Type[Expression]]],
 920]
 921ExpOrStr = t.Union[str, Expression]
 922
 923
 924class Condition(Expression):
 925    """Logical conditions like x AND y, or simply x"""
 926
 927
 928class Predicate(Condition):
 929    """Relationships like x = y, x > 1, x >= y."""
 930
 931
 932class DerivedTable(Expression):
 933    @property
 934    def selects(self) -> t.List[Expression]:
 935        return self.this.selects if isinstance(self.this, Query) else []
 936
 937    @property
 938    def named_selects(self) -> t.List[str]:
 939        return [select.output_name for select in self.selects]
 940
 941
 942class Query(Expression):
 943    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
 944        """
 945        Returns a `Subquery` that wraps around this query.
 946
 947        Example:
 948            >>> subquery = Select().select("x").from_("tbl").subquery()
 949            >>> Select().select("x").from_(subquery).sql()
 950            'SELECT x FROM (SELECT x FROM tbl)'
 951
 952        Args:
 953            alias: an optional alias for the subquery.
 954            copy: if `False`, modify this expression instance in-place.
 955        """
 956        instance = maybe_copy(self, copy)
 957        if not isinstance(alias, Expression):
 958            alias = TableAlias(this=to_identifier(alias)) if alias else None
 959
 960        return Subquery(this=instance, alias=alias)
 961
 962    def limit(
 963        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
 964    ) -> Select:
 965        """
 966        Adds a LIMIT clause to this query.
 967
 968        Example:
 969            >>> select("1").union(select("1")).limit(1).sql()
 970            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
 971
 972        Args:
 973            expression: the SQL code string to parse.
 974                This can also be an integer.
 975                If a `Limit` instance is passed, it will be used as-is.
 976                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
 977            dialect: the dialect used to parse the input expression.
 978            copy: if `False`, modify this expression instance in-place.
 979            opts: other options to use to parse the input expressions.
 980
 981        Returns:
 982            A limited Select expression.
 983        """
 984        return (
 985            select("*")
 986            .from_(self.subquery(alias="_l_0", copy=copy))
 987            .limit(expression, dialect=dialect, copy=False, **opts)
 988        )
 989
 990    @property
 991    def ctes(self) -> t.List[CTE]:
 992        """Returns a list of all the CTEs attached to this query."""
 993        with_ = self.args.get("with")
 994        return with_.expressions if with_ else []
 995
 996    @property
 997    def selects(self) -> t.List[Expression]:
 998        """Returns the query's projections."""
 999        raise NotImplementedError("Query objects must implement `selects`")
1000
1001    @property
1002    def named_selects(self) -> t.List[str]:
1003        """Returns the output names of the query's projections."""
1004        raise NotImplementedError("Query objects must implement `named_selects`")
1005
1006    def select(
1007        self,
1008        *expressions: t.Optional[ExpOrStr],
1009        append: bool = True,
1010        dialect: DialectType = None,
1011        copy: bool = True,
1012        **opts,
1013    ) -> Query:
1014        """
1015        Append to or set the SELECT expressions.
1016
1017        Example:
1018            >>> Select().select("x", "y").sql()
1019            'SELECT x, y'
1020
1021        Args:
1022            *expressions: the SQL code strings to parse.
1023                If an `Expression` instance is passed, it will be used as-is.
1024            append: if `True`, add to any existing expressions.
1025                Otherwise, this resets the expressions.
1026            dialect: the dialect used to parse the input expressions.
1027            copy: if `False`, modify this expression instance in-place.
1028            opts: other options to use to parse the input expressions.
1029
1030        Returns:
1031            The modified Query expression.
1032        """
1033        raise NotImplementedError("Query objects must implement `select`")
1034
1035    def with_(
1036        self,
1037        alias: ExpOrStr,
1038        as_: ExpOrStr,
1039        recursive: t.Optional[bool] = None,
1040        append: bool = True,
1041        dialect: DialectType = None,
1042        copy: bool = True,
1043        **opts,
1044    ) -> Query:
1045        """
1046        Append to or set the common table expressions.
1047
1048        Example:
1049            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1050            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1051
1052        Args:
1053            alias: the SQL code string to parse as the table name.
1054                If an `Expression` instance is passed, this is used as-is.
1055            as_: the SQL code string to parse as the table expression.
1056                If an `Expression` instance is passed, it will be used as-is.
1057            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1058            append: if `True`, add to any existing expressions.
1059                Otherwise, this resets the expressions.
1060            dialect: the dialect used to parse the input expression.
1061            copy: if `False`, modify this expression instance in-place.
1062            opts: other options to use to parse the input expressions.
1063
1064        Returns:
1065            The modified expression.
1066        """
1067        return _apply_cte_builder(
1068            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1069        )
1070
1071    def union(
1072        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1073    ) -> Union:
1074        """
1075        Builds a UNION expression.
1076
1077        Example:
1078            >>> import sqlglot
1079            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
1080            'SELECT * FROM foo UNION SELECT * FROM bla'
1081
1082        Args:
1083            expression: the SQL code string.
1084                If an `Expression` instance is passed, it will be used as-is.
1085            distinct: set the DISTINCT flag if and only if this is true.
1086            dialect: the dialect used to parse the input expression.
1087            opts: other options to use to parse the input expressions.
1088
1089        Returns:
1090            The new Union expression.
1091        """
1092        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1093
1094    def intersect(
1095        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1096    ) -> Intersect:
1097        """
1098        Builds an INTERSECT expression.
1099
1100        Example:
1101            >>> import sqlglot
1102            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
1103            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
1104
1105        Args:
1106            expression: the SQL code string.
1107                If an `Expression` instance is passed, it will be used as-is.
1108            distinct: set the DISTINCT flag if and only if this is true.
1109            dialect: the dialect used to parse the input expression.
1110            opts: other options to use to parse the input expressions.
1111
1112        Returns:
1113            The new Intersect expression.
1114        """
1115        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1116
1117    def except_(
1118        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1119    ) -> Except:
1120        """
1121        Builds an EXCEPT expression.
1122
1123        Example:
1124            >>> import sqlglot
1125            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
1126            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
1127
1128        Args:
1129            expression: the SQL code string.
1130                If an `Expression` instance is passed, it will be used as-is.
1131            distinct: set the DISTINCT flag if and only if this is true.
1132            dialect: the dialect used to parse the input expression.
1133            opts: other options to use to parse the input expressions.
1134
1135        Returns:
1136            The new Except expression.
1137        """
1138        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1139
1140
1141class UDTF(DerivedTable):
1142    @property
1143    def selects(self) -> t.List[Expression]:
1144        alias = self.args.get("alias")
1145        return alias.columns if alias else []
1146
1147
1148class Cache(Expression):
1149    arg_types = {
1150        "this": True,
1151        "lazy": False,
1152        "options": False,
1153        "expression": False,
1154    }
1155
1156
1157class Uncache(Expression):
1158    arg_types = {"this": True, "exists": False}
1159
1160
1161class Refresh(Expression):
1162    pass
1163
1164
1165class DDL(Expression):
1166    @property
1167    def ctes(self) -> t.List[CTE]:
1168        """Returns a list of all the CTEs attached to this statement."""
1169        with_ = self.args.get("with")
1170        return with_.expressions if with_ else []
1171
1172    @property
1173    def selects(self) -> t.List[Expression]:
1174        """If this statement contains a query (e.g. a CTAS), this returns the query's projections."""
1175        return self.expression.selects if isinstance(self.expression, Query) else []
1176
1177    @property
1178    def named_selects(self) -> t.List[str]:
1179        """
1180        If this statement contains a query (e.g. a CTAS), this returns the output
1181        names of the query's projections.
1182        """
1183        return self.expression.named_selects if isinstance(self.expression, Query) else []
1184
1185
1186class DML(Expression):
1187    def returning(
1188        self,
1189        expression: ExpOrStr,
1190        dialect: DialectType = None,
1191        copy: bool = True,
1192        **opts,
1193    ) -> DML:
1194        """
1195        Set the RETURNING expression. Not supported by all dialects.
1196
1197        Example:
1198            >>> delete("tbl").returning("*", dialect="postgres").sql()
1199            'DELETE FROM tbl RETURNING *'
1200
1201        Args:
1202            expression: the SQL code strings to parse.
1203                If an `Expression` instance is passed, it will be used as-is.
1204            dialect: the dialect used to parse the input expressions.
1205            copy: if `False`, modify this expression instance in-place.
1206            opts: other options to use to parse the input expressions.
1207
1208        Returns:
1209            Delete: the modified expression.
1210        """
1211        return _apply_builder(
1212            expression=expression,
1213            instance=self,
1214            arg="returning",
1215            prefix="RETURNING",
1216            dialect=dialect,
1217            copy=copy,
1218            into=Returning,
1219            **opts,
1220        )
1221
1222
1223class Create(DDL):
1224    arg_types = {
1225        "with": False,
1226        "this": True,
1227        "kind": True,
1228        "expression": False,
1229        "exists": False,
1230        "properties": False,
1231        "replace": False,
1232        "unique": False,
1233        "indexes": False,
1234        "no_schema_binding": False,
1235        "begin": False,
1236        "end": False,
1237        "clone": False,
1238    }
1239
1240    @property
1241    def kind(self) -> t.Optional[str]:
1242        kind = self.args.get("kind")
1243        return kind and kind.upper()
1244
1245
1246class SequenceProperties(Expression):
1247    arg_types = {
1248        "increment": False,
1249        "minvalue": False,
1250        "maxvalue": False,
1251        "cache": False,
1252        "start": False,
1253        "owned": False,
1254        "options": False,
1255    }
1256
1257
1258class TruncateTable(Expression):
1259    arg_types = {
1260        "expressions": True,
1261        "is_database": False,
1262        "exists": False,
1263        "only": False,
1264        "cluster": False,
1265        "identity": False,
1266        "option": False,
1267        "partition": False,
1268    }
1269
1270
1271# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1272# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1273# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1274class Clone(Expression):
1275    arg_types = {"this": True, "shallow": False, "copy": False}
1276
1277
1278class Describe(Expression):
1279    arg_types = {"this": True, "extended": False, "kind": False, "expressions": False}
1280
1281
1282class Kill(Expression):
1283    arg_types = {"this": True, "kind": False}
1284
1285
1286class Pragma(Expression):
1287    pass
1288
1289
1290class Set(Expression):
1291    arg_types = {"expressions": False, "unset": False, "tag": False}
1292
1293
1294class Heredoc(Expression):
1295    arg_types = {"this": True, "tag": False}
1296
1297
1298class SetItem(Expression):
1299    arg_types = {
1300        "this": False,
1301        "expressions": False,
1302        "kind": False,
1303        "collate": False,  # MySQL SET NAMES statement
1304        "global": False,
1305    }
1306
1307
1308class Show(Expression):
1309    arg_types = {
1310        "this": True,
1311        "history": False,
1312        "terse": False,
1313        "target": False,
1314        "offset": False,
1315        "starts_with": False,
1316        "limit": False,
1317        "from": False,
1318        "like": False,
1319        "where": False,
1320        "db": False,
1321        "scope": False,
1322        "scope_kind": False,
1323        "full": False,
1324        "mutex": False,
1325        "query": False,
1326        "channel": False,
1327        "global": False,
1328        "log": False,
1329        "position": False,
1330        "types": False,
1331    }
1332
1333
1334class UserDefinedFunction(Expression):
1335    arg_types = {"this": True, "expressions": False, "wrapped": False}
1336
1337
1338class CharacterSet(Expression):
1339    arg_types = {"this": True, "default": False}
1340
1341
1342class With(Expression):
1343    arg_types = {"expressions": True, "recursive": False}
1344
1345    @property
1346    def recursive(self) -> bool:
1347        return bool(self.args.get("recursive"))
1348
1349
1350class WithinGroup(Expression):
1351    arg_types = {"this": True, "expression": False}
1352
1353
1354# clickhouse supports scalar ctes
1355# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1356class CTE(DerivedTable):
1357    arg_types = {"this": True, "alias": True, "scalar": False}
1358
1359
1360class TableAlias(Expression):
1361    arg_types = {"this": False, "columns": False}
1362
1363    @property
1364    def columns(self):
1365        return self.args.get("columns") or []
1366
1367
1368class BitString(Condition):
1369    pass
1370
1371
1372class HexString(Condition):
1373    pass
1374
1375
1376class ByteString(Condition):
1377    pass
1378
1379
1380class RawString(Condition):
1381    pass
1382
1383
1384class UnicodeString(Condition):
1385    arg_types = {"this": True, "escape": False}
1386
1387
1388class Column(Condition):
1389    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1390
1391    @property
1392    def table(self) -> str:
1393        return self.text("table")
1394
1395    @property
1396    def db(self) -> str:
1397        return self.text("db")
1398
1399    @property
1400    def catalog(self) -> str:
1401        return self.text("catalog")
1402
1403    @property
1404    def output_name(self) -> str:
1405        return self.name
1406
1407    @property
1408    def parts(self) -> t.List[Identifier]:
1409        """Return the parts of a column in order catalog, db, table, name."""
1410        return [
1411            t.cast(Identifier, self.args[part])
1412            for part in ("catalog", "db", "table", "this")
1413            if self.args.get(part)
1414        ]
1415
1416    def to_dot(self) -> Dot | Identifier:
1417        """Converts the column into a dot expression."""
1418        parts = self.parts
1419        parent = self.parent
1420
1421        while parent:
1422            if isinstance(parent, Dot):
1423                parts.append(parent.expression)
1424            parent = parent.parent
1425
1426        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1427
1428
1429class ColumnPosition(Expression):
1430    arg_types = {"this": False, "position": True}
1431
1432
1433class ColumnDef(Expression):
1434    arg_types = {
1435        "this": True,
1436        "kind": False,
1437        "constraints": False,
1438        "exists": False,
1439        "position": False,
1440    }
1441
1442    @property
1443    def constraints(self) -> t.List[ColumnConstraint]:
1444        return self.args.get("constraints") or []
1445
1446    @property
1447    def kind(self) -> t.Optional[DataType]:
1448        return self.args.get("kind")
1449
1450
1451class AlterColumn(Expression):
1452    arg_types = {
1453        "this": True,
1454        "dtype": False,
1455        "collate": False,
1456        "using": False,
1457        "default": False,
1458        "drop": False,
1459        "comment": False,
1460    }
1461
1462
1463class RenameColumn(Expression):
1464    arg_types = {"this": True, "to": True, "exists": False}
1465
1466
1467class RenameTable(Expression):
1468    pass
1469
1470
1471class SwapTable(Expression):
1472    pass
1473
1474
1475class Comment(Expression):
1476    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1477
1478
1479class Comprehension(Expression):
1480    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1481
1482
1483# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1484class MergeTreeTTLAction(Expression):
1485    arg_types = {
1486        "this": True,
1487        "delete": False,
1488        "recompress": False,
1489        "to_disk": False,
1490        "to_volume": False,
1491    }
1492
1493
1494# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1495class MergeTreeTTL(Expression):
1496    arg_types = {
1497        "expressions": True,
1498        "where": False,
1499        "group": False,
1500        "aggregates": False,
1501    }
1502
1503
1504# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1505class IndexConstraintOption(Expression):
1506    arg_types = {
1507        "key_block_size": False,
1508        "using": False,
1509        "parser": False,
1510        "comment": False,
1511        "visible": False,
1512        "engine_attr": False,
1513        "secondary_engine_attr": False,
1514    }
1515
1516
1517class ColumnConstraint(Expression):
1518    arg_types = {"this": False, "kind": True}
1519
1520    @property
1521    def kind(self) -> ColumnConstraintKind:
1522        return self.args["kind"]
1523
1524
1525class ColumnConstraintKind(Expression):
1526    pass
1527
1528
1529class AutoIncrementColumnConstraint(ColumnConstraintKind):
1530    pass
1531
1532
1533class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1534    arg_types = {"this": True, "expression": True}
1535
1536
1537class CaseSpecificColumnConstraint(ColumnConstraintKind):
1538    arg_types = {"not_": True}
1539
1540
1541class CharacterSetColumnConstraint(ColumnConstraintKind):
1542    arg_types = {"this": True}
1543
1544
1545class CheckColumnConstraint(ColumnConstraintKind):
1546    arg_types = {"this": True, "enforced": False}
1547
1548
1549class ClusteredColumnConstraint(ColumnConstraintKind):
1550    pass
1551
1552
1553class CollateColumnConstraint(ColumnConstraintKind):
1554    pass
1555
1556
1557class CommentColumnConstraint(ColumnConstraintKind):
1558    pass
1559
1560
1561class CompressColumnConstraint(ColumnConstraintKind):
1562    pass
1563
1564
1565class DateFormatColumnConstraint(ColumnConstraintKind):
1566    arg_types = {"this": True}
1567
1568
1569class DefaultColumnConstraint(ColumnConstraintKind):
1570    pass
1571
1572
1573class EncodeColumnConstraint(ColumnConstraintKind):
1574    pass
1575
1576
1577class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1578    # this: True -> ALWAYS, this: False -> BY DEFAULT
1579    arg_types = {
1580        "this": False,
1581        "expression": False,
1582        "on_null": False,
1583        "start": False,
1584        "increment": False,
1585        "minvalue": False,
1586        "maxvalue": False,
1587        "cycle": False,
1588    }
1589
1590
1591class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1592    arg_types = {"start": False, "hidden": False}
1593
1594
1595# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1596class IndexColumnConstraint(ColumnConstraintKind):
1597    arg_types = {
1598        "this": False,
1599        "schema": True,
1600        "kind": False,
1601        "index_type": False,
1602        "options": False,
1603    }
1604
1605
1606class InlineLengthColumnConstraint(ColumnConstraintKind):
1607    pass
1608
1609
1610class NonClusteredColumnConstraint(ColumnConstraintKind):
1611    pass
1612
1613
1614class NotForReplicationColumnConstraint(ColumnConstraintKind):
1615    arg_types = {}
1616
1617
1618class NotNullColumnConstraint(ColumnConstraintKind):
1619    arg_types = {"allow_null": False}
1620
1621
1622# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1623class OnUpdateColumnConstraint(ColumnConstraintKind):
1624    pass
1625
1626
1627# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1628class TransformColumnConstraint(ColumnConstraintKind):
1629    pass
1630
1631
1632class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1633    arg_types = {"desc": False}
1634
1635
1636class TitleColumnConstraint(ColumnConstraintKind):
1637    pass
1638
1639
1640class UniqueColumnConstraint(ColumnConstraintKind):
1641    arg_types = {"this": False, "index_type": False, "on_conflict": False}
1642
1643
1644class UppercaseColumnConstraint(ColumnConstraintKind):
1645    arg_types: t.Dict[str, t.Any] = {}
1646
1647
1648class PathColumnConstraint(ColumnConstraintKind):
1649    pass
1650
1651
1652# computed column expression
1653# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1654class ComputedColumnConstraint(ColumnConstraintKind):
1655    arg_types = {"this": True, "persisted": False, "not_null": False}
1656
1657
1658class Constraint(Expression):
1659    arg_types = {"this": True, "expressions": True}
1660
1661
1662class Delete(DML):
1663    arg_types = {
1664        "with": False,
1665        "this": False,
1666        "using": False,
1667        "where": False,
1668        "returning": False,
1669        "limit": False,
1670        "tables": False,  # Multiple-Table Syntax (MySQL)
1671    }
1672
1673    def delete(
1674        self,
1675        table: ExpOrStr,
1676        dialect: DialectType = None,
1677        copy: bool = True,
1678        **opts,
1679    ) -> Delete:
1680        """
1681        Create a DELETE expression or replace the table on an existing DELETE expression.
1682
1683        Example:
1684            >>> delete("tbl").sql()
1685            'DELETE FROM tbl'
1686
1687        Args:
1688            table: the table from which to delete.
1689            dialect: the dialect used to parse the input expression.
1690            copy: if `False`, modify this expression instance in-place.
1691            opts: other options to use to parse the input expressions.
1692
1693        Returns:
1694            Delete: the modified expression.
1695        """
1696        return _apply_builder(
1697            expression=table,
1698            instance=self,
1699            arg="this",
1700            dialect=dialect,
1701            into=Table,
1702            copy=copy,
1703            **opts,
1704        )
1705
1706    def where(
1707        self,
1708        *expressions: t.Optional[ExpOrStr],
1709        append: bool = True,
1710        dialect: DialectType = None,
1711        copy: bool = True,
1712        **opts,
1713    ) -> Delete:
1714        """
1715        Append to or set the WHERE expressions.
1716
1717        Example:
1718            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1719            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1720
1721        Args:
1722            *expressions: the SQL code strings to parse.
1723                If an `Expression` instance is passed, it will be used as-is.
1724                Multiple expressions are combined with an AND operator.
1725            append: if `True`, AND the new expressions to any existing expression.
1726                Otherwise, this resets the expression.
1727            dialect: the dialect used to parse the input expressions.
1728            copy: if `False`, modify this expression instance in-place.
1729            opts: other options to use to parse the input expressions.
1730
1731        Returns:
1732            Delete: the modified expression.
1733        """
1734        return _apply_conjunction_builder(
1735            *expressions,
1736            instance=self,
1737            arg="where",
1738            append=append,
1739            into=Where,
1740            dialect=dialect,
1741            copy=copy,
1742            **opts,
1743        )
1744
1745
1746class Drop(Expression):
1747    arg_types = {
1748        "this": False,
1749        "kind": False,
1750        "exists": False,
1751        "temporary": False,
1752        "materialized": False,
1753        "cascade": False,
1754        "constraints": False,
1755        "purge": False,
1756    }
1757
1758
1759class Filter(Expression):
1760    arg_types = {"this": True, "expression": True}
1761
1762
1763class Check(Expression):
1764    pass
1765
1766
1767# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1768class Connect(Expression):
1769    arg_types = {"start": False, "connect": True}
1770
1771
1772class Prior(Expression):
1773    pass
1774
1775
1776class Directory(Expression):
1777    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1778    arg_types = {"this": True, "local": False, "row_format": False}
1779
1780
1781class ForeignKey(Expression):
1782    arg_types = {
1783        "expressions": True,
1784        "reference": False,
1785        "delete": False,
1786        "update": False,
1787    }
1788
1789
1790class ColumnPrefix(Expression):
1791    arg_types = {"this": True, "expression": True}
1792
1793
1794class PrimaryKey(Expression):
1795    arg_types = {"expressions": True, "options": False}
1796
1797
1798# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1799# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1800class Into(Expression):
1801    arg_types = {"this": True, "temporary": False, "unlogged": False}
1802
1803
1804class From(Expression):
1805    @property
1806    def name(self) -> str:
1807        return self.this.name
1808
1809    @property
1810    def alias_or_name(self) -> str:
1811        return self.this.alias_or_name
1812
1813
1814class Having(Expression):
1815    pass
1816
1817
1818class Hint(Expression):
1819    arg_types = {"expressions": True}
1820
1821
1822class JoinHint(Expression):
1823    arg_types = {"this": True, "expressions": True}
1824
1825
1826class Identifier(Expression):
1827    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1828
1829    @property
1830    def quoted(self) -> bool:
1831        return bool(self.args.get("quoted"))
1832
1833    @property
1834    def hashable_args(self) -> t.Any:
1835        return (self.this, self.quoted)
1836
1837    @property
1838    def output_name(self) -> str:
1839        return self.name
1840
1841
1842# https://www.postgresql.org/docs/current/indexes-opclass.html
1843class Opclass(Expression):
1844    arg_types = {"this": True, "expression": True}
1845
1846
1847class Index(Expression):
1848    arg_types = {
1849        "this": False,
1850        "table": False,
1851        "using": False,
1852        "where": False,
1853        "columns": False,
1854        "unique": False,
1855        "primary": False,
1856        "amp": False,  # teradata
1857        "include": False,
1858        "partition_by": False,  # teradata
1859    }
1860
1861
1862class Insert(DDL, DML):
1863    arg_types = {
1864        "hint": False,
1865        "with": False,
1866        "this": True,
1867        "expression": False,
1868        "conflict": False,
1869        "returning": False,
1870        "overwrite": False,
1871        "exists": False,
1872        "partition": False,
1873        "alternative": False,
1874        "where": False,
1875        "ignore": False,
1876        "by_name": False,
1877    }
1878
1879    def with_(
1880        self,
1881        alias: ExpOrStr,
1882        as_: ExpOrStr,
1883        recursive: t.Optional[bool] = None,
1884        append: bool = True,
1885        dialect: DialectType = None,
1886        copy: bool = True,
1887        **opts,
1888    ) -> Insert:
1889        """
1890        Append to or set the common table expressions.
1891
1892        Example:
1893            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1894            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1895
1896        Args:
1897            alias: the SQL code string to parse as the table name.
1898                If an `Expression` instance is passed, this is used as-is.
1899            as_: the SQL code string to parse as the table expression.
1900                If an `Expression` instance is passed, it will be used as-is.
1901            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1902            append: if `True`, add to any existing expressions.
1903                Otherwise, this resets the expressions.
1904            dialect: the dialect used to parse the input expression.
1905            copy: if `False`, modify this expression instance in-place.
1906            opts: other options to use to parse the input expressions.
1907
1908        Returns:
1909            The modified expression.
1910        """
1911        return _apply_cte_builder(
1912            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1913        )
1914
1915
1916class OnConflict(Expression):
1917    arg_types = {
1918        "duplicate": False,
1919        "expressions": False,
1920        "action": False,
1921        "conflict_keys": False,
1922        "constraint": False,
1923    }
1924
1925
1926class Returning(Expression):
1927    arg_types = {"expressions": True, "into": False}
1928
1929
1930# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1931class Introducer(Expression):
1932    arg_types = {"this": True, "expression": True}
1933
1934
1935# national char, like n'utf8'
1936class National(Expression):
1937    pass
1938
1939
1940class LoadData(Expression):
1941    arg_types = {
1942        "this": True,
1943        "local": False,
1944        "overwrite": False,
1945        "inpath": True,
1946        "partition": False,
1947        "input_format": False,
1948        "serde": False,
1949    }
1950
1951
1952class Partition(Expression):
1953    arg_types = {"expressions": True}
1954
1955
1956class PartitionRange(Expression):
1957    arg_types = {"this": True, "expression": True}
1958
1959
1960class Fetch(Expression):
1961    arg_types = {
1962        "direction": False,
1963        "count": False,
1964        "percent": False,
1965        "with_ties": False,
1966    }
1967
1968
1969class Group(Expression):
1970    arg_types = {
1971        "expressions": False,
1972        "grouping_sets": False,
1973        "cube": False,
1974        "rollup": False,
1975        "totals": False,
1976        "all": False,
1977    }
1978
1979
1980class Lambda(Expression):
1981    arg_types = {"this": True, "expressions": True}
1982
1983
1984class Limit(Expression):
1985    arg_types = {"this": False, "expression": True, "offset": False, "expressions": False}
1986
1987
1988class Literal(Condition):
1989    arg_types = {"this": True, "is_string": True}
1990
1991    @property
1992    def hashable_args(self) -> t.Any:
1993        return (self.this, self.args.get("is_string"))
1994
1995    @classmethod
1996    def number(cls, number) -> Literal:
1997        return cls(this=str(number), is_string=False)
1998
1999    @classmethod
2000    def string(cls, string) -> Literal:
2001        return cls(this=str(string), is_string=True)
2002
2003    @property
2004    def output_name(self) -> str:
2005        return self.name
2006
2007
2008class Join(Expression):
2009    arg_types = {
2010        "this": True,
2011        "on": False,
2012        "side": False,
2013        "kind": False,
2014        "using": False,
2015        "method": False,
2016        "global": False,
2017        "hint": False,
2018    }
2019
2020    @property
2021    def method(self) -> str:
2022        return self.text("method").upper()
2023
2024    @property
2025    def kind(self) -> str:
2026        return self.text("kind").upper()
2027
2028    @property
2029    def side(self) -> str:
2030        return self.text("side").upper()
2031
2032    @property
2033    def hint(self) -> str:
2034        return self.text("hint").upper()
2035
2036    @property
2037    def alias_or_name(self) -> str:
2038        return self.this.alias_or_name
2039
2040    def on(
2041        self,
2042        *expressions: t.Optional[ExpOrStr],
2043        append: bool = True,
2044        dialect: DialectType = None,
2045        copy: bool = True,
2046        **opts,
2047    ) -> Join:
2048        """
2049        Append to or set the ON expressions.
2050
2051        Example:
2052            >>> import sqlglot
2053            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
2054            'JOIN x ON y = 1'
2055
2056        Args:
2057            *expressions: the SQL code strings to parse.
2058                If an `Expression` instance is passed, it will be used as-is.
2059                Multiple expressions are combined with an AND operator.
2060            append: if `True`, AND the new expressions to any existing expression.
2061                Otherwise, this resets the expression.
2062            dialect: the dialect used to parse the input expressions.
2063            copy: if `False`, modify this expression instance in-place.
2064            opts: other options to use to parse the input expressions.
2065
2066        Returns:
2067            The modified Join expression.
2068        """
2069        join = _apply_conjunction_builder(
2070            *expressions,
2071            instance=self,
2072            arg="on",
2073            append=append,
2074            dialect=dialect,
2075            copy=copy,
2076            **opts,
2077        )
2078
2079        if join.kind == "CROSS":
2080            join.set("kind", None)
2081
2082        return join
2083
2084    def using(
2085        self,
2086        *expressions: t.Optional[ExpOrStr],
2087        append: bool = True,
2088        dialect: DialectType = None,
2089        copy: bool = True,
2090        **opts,
2091    ) -> Join:
2092        """
2093        Append to or set the USING expressions.
2094
2095        Example:
2096            >>> import sqlglot
2097            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
2098            'JOIN x USING (foo, bla)'
2099
2100        Args:
2101            *expressions: the SQL code strings to parse.
2102                If an `Expression` instance is passed, it will be used as-is.
2103            append: if `True`, concatenate the new expressions to the existing "using" list.
2104                Otherwise, this resets the expression.
2105            dialect: the dialect used to parse the input expressions.
2106            copy: if `False`, modify this expression instance in-place.
2107            opts: other options to use to parse the input expressions.
2108
2109        Returns:
2110            The modified Join expression.
2111        """
2112        join = _apply_list_builder(
2113            *expressions,
2114            instance=self,
2115            arg="using",
2116            append=append,
2117            dialect=dialect,
2118            copy=copy,
2119            **opts,
2120        )
2121
2122        if join.kind == "CROSS":
2123            join.set("kind", None)
2124
2125        return join
2126
2127
2128class Lateral(UDTF):
2129    arg_types = {
2130        "this": True,
2131        "view": False,
2132        "outer": False,
2133        "alias": False,
2134        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
2135    }
2136
2137
2138class MatchRecognize(Expression):
2139    arg_types = {
2140        "partition_by": False,
2141        "order": False,
2142        "measures": False,
2143        "rows": False,
2144        "after": False,
2145        "pattern": False,
2146        "define": False,
2147        "alias": False,
2148    }
2149
2150
2151# Clickhouse FROM FINAL modifier
2152# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
2153class Final(Expression):
2154    pass
2155
2156
2157class Offset(Expression):
2158    arg_types = {"this": False, "expression": True, "expressions": False}
2159
2160
2161class Order(Expression):
2162    arg_types = {
2163        "this": False,
2164        "expressions": True,
2165        "interpolate": False,
2166        "siblings": False,
2167    }
2168
2169
2170# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
2171class WithFill(Expression):
2172    arg_types = {"from": False, "to": False, "step": False}
2173
2174
2175# hive specific sorts
2176# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
2177class Cluster(Order):
2178    pass
2179
2180
2181class Distribute(Order):
2182    pass
2183
2184
2185class Sort(Order):
2186    pass
2187
2188
2189class Ordered(Expression):
2190    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2191
2192
2193class Property(Expression):
2194    arg_types = {"this": True, "value": True}
2195
2196
2197class AlgorithmProperty(Property):
2198    arg_types = {"this": True}
2199
2200
2201class AutoIncrementProperty(Property):
2202    arg_types = {"this": True}
2203
2204
2205# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2206class AutoRefreshProperty(Property):
2207    arg_types = {"this": True}
2208
2209
2210class BlockCompressionProperty(Property):
2211    arg_types = {
2212        "autotemp": False,
2213        "always": False,
2214        "default": False,
2215        "manual": False,
2216        "never": False,
2217    }
2218
2219
2220class CharacterSetProperty(Property):
2221    arg_types = {"this": True, "default": True}
2222
2223
2224class ChecksumProperty(Property):
2225    arg_types = {"on": False, "default": False}
2226
2227
2228class CollateProperty(Property):
2229    arg_types = {"this": True, "default": False}
2230
2231
2232class CopyGrantsProperty(Property):
2233    arg_types = {}
2234
2235
2236class DataBlocksizeProperty(Property):
2237    arg_types = {
2238        "size": False,
2239        "units": False,
2240        "minimum": False,
2241        "maximum": False,
2242        "default": False,
2243    }
2244
2245
2246class DefinerProperty(Property):
2247    arg_types = {"this": True}
2248
2249
2250class DistKeyProperty(Property):
2251    arg_types = {"this": True}
2252
2253
2254class DistStyleProperty(Property):
2255    arg_types = {"this": True}
2256
2257
2258class EngineProperty(Property):
2259    arg_types = {"this": True}
2260
2261
2262class HeapProperty(Property):
2263    arg_types = {}
2264
2265
2266class ToTableProperty(Property):
2267    arg_types = {"this": True}
2268
2269
2270class ExecuteAsProperty(Property):
2271    arg_types = {"this": True}
2272
2273
2274class ExternalProperty(Property):
2275    arg_types = {"this": False}
2276
2277
2278class FallbackProperty(Property):
2279    arg_types = {"no": True, "protection": False}
2280
2281
2282class FileFormatProperty(Property):
2283    arg_types = {"this": True}
2284
2285
2286class FreespaceProperty(Property):
2287    arg_types = {"this": True, "percent": False}
2288
2289
2290class GlobalProperty(Property):
2291    arg_types = {}
2292
2293
2294class InheritsProperty(Property):
2295    arg_types = {"expressions": True}
2296
2297
2298class InputModelProperty(Property):
2299    arg_types = {"this": True}
2300
2301
2302class OutputModelProperty(Property):
2303    arg_types = {"this": True}
2304
2305
2306class IsolatedLoadingProperty(Property):
2307    arg_types = {
2308        "no": False,
2309        "concurrent": False,
2310        "for_all": False,
2311        "for_insert": False,
2312        "for_none": False,
2313    }
2314
2315
2316class JournalProperty(Property):
2317    arg_types = {
2318        "no": False,
2319        "dual": False,
2320        "before": False,
2321        "local": False,
2322        "after": False,
2323    }
2324
2325
2326class LanguageProperty(Property):
2327    arg_types = {"this": True}
2328
2329
2330# spark ddl
2331class ClusteredByProperty(Property):
2332    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2333
2334
2335class DictProperty(Property):
2336    arg_types = {"this": True, "kind": True, "settings": False}
2337
2338
2339class DictSubProperty(Property):
2340    pass
2341
2342
2343class DictRange(Property):
2344    arg_types = {"this": True, "min": True, "max": True}
2345
2346
2347# Clickhouse CREATE ... ON CLUSTER modifier
2348# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2349class OnCluster(Property):
2350    arg_types = {"this": True}
2351
2352
2353class LikeProperty(Property):
2354    arg_types = {"this": True, "expressions": False}
2355
2356
2357class LocationProperty(Property):
2358    arg_types = {"this": True}
2359
2360
2361class LockProperty(Property):
2362    arg_types = {"this": True}
2363
2364
2365class LockingProperty(Property):
2366    arg_types = {
2367        "this": False,
2368        "kind": True,
2369        "for_or_in": False,
2370        "lock_type": True,
2371        "override": False,
2372    }
2373
2374
2375class LogProperty(Property):
2376    arg_types = {"no": True}
2377
2378
2379class MaterializedProperty(Property):
2380    arg_types = {"this": False}
2381
2382
2383class MergeBlockRatioProperty(Property):
2384    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2385
2386
2387class NoPrimaryIndexProperty(Property):
2388    arg_types = {}
2389
2390
2391class OnProperty(Property):
2392    arg_types = {"this": True}
2393
2394
2395class OnCommitProperty(Property):
2396    arg_types = {"delete": False}
2397
2398
2399class PartitionedByProperty(Property):
2400    arg_types = {"this": True}
2401
2402
2403# https://www.postgresql.org/docs/current/sql-createtable.html
2404class PartitionBoundSpec(Expression):
2405    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2406    arg_types = {
2407        "this": False,
2408        "expression": False,
2409        "from_expressions": False,
2410        "to_expressions": False,
2411    }
2412
2413
2414class PartitionedOfProperty(Property):
2415    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2416    arg_types = {"this": True, "expression": True}
2417
2418
2419class RemoteWithConnectionModelProperty(Property):
2420    arg_types = {"this": True}
2421
2422
2423class ReturnsProperty(Property):
2424    arg_types = {"this": True, "is_table": False, "table": False}
2425
2426
2427class RowFormatProperty(Property):
2428    arg_types = {"this": True}
2429
2430
2431class RowFormatDelimitedProperty(Property):
2432    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2433    arg_types = {
2434        "fields": False,
2435        "escaped": False,
2436        "collection_items": False,
2437        "map_keys": False,
2438        "lines": False,
2439        "null": False,
2440        "serde": False,
2441    }
2442
2443
2444class RowFormatSerdeProperty(Property):
2445    arg_types = {"this": True, "serde_properties": False}
2446
2447
2448# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2449class QueryTransform(Expression):
2450    arg_types = {
2451        "expressions": True,
2452        "command_script": True,
2453        "schema": False,
2454        "row_format_before": False,
2455        "record_writer": False,
2456        "row_format_after": False,
2457        "record_reader": False,
2458    }
2459
2460
2461class SampleProperty(Property):
2462    arg_types = {"this": True}
2463
2464
2465class SchemaCommentProperty(Property):
2466    arg_types = {"this": True}
2467
2468
2469class SerdeProperties(Property):
2470    arg_types = {"expressions": True}
2471
2472
2473class SetProperty(Property):
2474    arg_types = {"multi": True}
2475
2476
2477class SharingProperty(Property):
2478    arg_types = {"this": False}
2479
2480
2481class SetConfigProperty(Property):
2482    arg_types = {"this": True}
2483
2484
2485class SettingsProperty(Property):
2486    arg_types = {"expressions": True}
2487
2488
2489class SortKeyProperty(Property):
2490    arg_types = {"this": True, "compound": False}
2491
2492
2493class SqlReadWriteProperty(Property):
2494    arg_types = {"this": True}
2495
2496
2497class SqlSecurityProperty(Property):
2498    arg_types = {"definer": True}
2499
2500
2501class StabilityProperty(Property):
2502    arg_types = {"this": True}
2503
2504
2505class TemporaryProperty(Property):
2506    arg_types = {"this": False}
2507
2508
2509class TransformModelProperty(Property):
2510    arg_types = {"expressions": True}
2511
2512
2513class TransientProperty(Property):
2514    arg_types = {"this": False}
2515
2516
2517class UnloggedProperty(Property):
2518    arg_types = {}
2519
2520
2521class VolatileProperty(Property):
2522    arg_types = {"this": False}
2523
2524
2525class WithDataProperty(Property):
2526    arg_types = {"no": True, "statistics": False}
2527
2528
2529class WithJournalTableProperty(Property):
2530    arg_types = {"this": True}
2531
2532
2533class WithSystemVersioningProperty(Property):
2534    # this -> history table name, expression -> data consistency check
2535    arg_types = {"this": False, "expression": False}
2536
2537
2538class Properties(Expression):
2539    arg_types = {"expressions": True}
2540
2541    NAME_TO_PROPERTY = {
2542        "ALGORITHM": AlgorithmProperty,
2543        "AUTO_INCREMENT": AutoIncrementProperty,
2544        "CHARACTER SET": CharacterSetProperty,
2545        "CLUSTERED_BY": ClusteredByProperty,
2546        "COLLATE": CollateProperty,
2547        "COMMENT": SchemaCommentProperty,
2548        "DEFINER": DefinerProperty,
2549        "DISTKEY": DistKeyProperty,
2550        "DISTSTYLE": DistStyleProperty,
2551        "ENGINE": EngineProperty,
2552        "EXECUTE AS": ExecuteAsProperty,
2553        "FORMAT": FileFormatProperty,
2554        "LANGUAGE": LanguageProperty,
2555        "LOCATION": LocationProperty,
2556        "LOCK": LockProperty,
2557        "PARTITIONED_BY": PartitionedByProperty,
2558        "RETURNS": ReturnsProperty,
2559        "ROW_FORMAT": RowFormatProperty,
2560        "SORTKEY": SortKeyProperty,
2561    }
2562
2563    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2564
2565    # CREATE property locations
2566    # Form: schema specified
2567    #   create [POST_CREATE]
2568    #     table a [POST_NAME]
2569    #     (b int) [POST_SCHEMA]
2570    #     with ([POST_WITH])
2571    #     index (b) [POST_INDEX]
2572    #
2573    # Form: alias selection
2574    #   create [POST_CREATE]
2575    #     table a [POST_NAME]
2576    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2577    #     index (c) [POST_INDEX]
2578    class Location(AutoName):
2579        POST_CREATE = auto()
2580        POST_NAME = auto()
2581        POST_SCHEMA = auto()
2582        POST_WITH = auto()
2583        POST_ALIAS = auto()
2584        POST_EXPRESSION = auto()
2585        POST_INDEX = auto()
2586        UNSUPPORTED = auto()
2587
2588    @classmethod
2589    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2590        expressions = []
2591        for key, value in properties_dict.items():
2592            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2593            if property_cls:
2594                expressions.append(property_cls(this=convert(value)))
2595            else:
2596                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2597
2598        return cls(expressions=expressions)
2599
2600
2601class Qualify(Expression):
2602    pass
2603
2604
2605class InputOutputFormat(Expression):
2606    arg_types = {"input_format": False, "output_format": False}
2607
2608
2609# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2610class Return(Expression):
2611    pass
2612
2613
2614class Reference(Expression):
2615    arg_types = {"this": True, "expressions": False, "options": False}
2616
2617
2618class Tuple(Expression):
2619    arg_types = {"expressions": False}
2620
2621    def isin(
2622        self,
2623        *expressions: t.Any,
2624        query: t.Optional[ExpOrStr] = None,
2625        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2626        copy: bool = True,
2627        **opts,
2628    ) -> In:
2629        return In(
2630            this=maybe_copy(self, copy),
2631            expressions=[convert(e, copy=copy) for e in expressions],
2632            query=maybe_parse(query, copy=copy, **opts) if query else None,
2633            unnest=(
2634                Unnest(
2635                    expressions=[
2636                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
2637                        for e in ensure_list(unnest)
2638                    ]
2639                )
2640                if unnest
2641                else None
2642            ),
2643        )
2644
2645
2646QUERY_MODIFIERS = {
2647    "match": False,
2648    "laterals": False,
2649    "joins": False,
2650    "connect": False,
2651    "pivots": False,
2652    "prewhere": False,
2653    "where": False,
2654    "group": False,
2655    "having": False,
2656    "qualify": False,
2657    "windows": False,
2658    "distribute": False,
2659    "sort": False,
2660    "cluster": False,
2661    "order": False,
2662    "limit": False,
2663    "offset": False,
2664    "locks": False,
2665    "sample": False,
2666    "settings": False,
2667    "format": False,
2668    "options": False,
2669}
2670
2671
2672# https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16
2673# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16
2674class QueryOption(Expression):
2675    arg_types = {"this": True, "expression": False}
2676
2677
2678# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2679class WithTableHint(Expression):
2680    arg_types = {"expressions": True}
2681
2682
2683# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2684class IndexTableHint(Expression):
2685    arg_types = {"this": True, "expressions": False, "target": False}
2686
2687
2688# https://docs.snowflake.com/en/sql-reference/constructs/at-before
2689class HistoricalData(Expression):
2690    arg_types = {"this": True, "kind": True, "expression": True}
2691
2692
2693class Table(Expression):
2694    arg_types = {
2695        "this": False,
2696        "alias": False,
2697        "db": False,
2698        "catalog": False,
2699        "laterals": False,
2700        "joins": False,
2701        "pivots": False,
2702        "hints": False,
2703        "system_time": False,
2704        "version": False,
2705        "format": False,
2706        "pattern": False,
2707        "ordinality": False,
2708        "when": False,
2709        "only": False,
2710    }
2711
2712    @property
2713    def name(self) -> str:
2714        if isinstance(self.this, Func):
2715            return ""
2716        return self.this.name
2717
2718    @property
2719    def db(self) -> str:
2720        return self.text("db")
2721
2722    @property
2723    def catalog(self) -> str:
2724        return self.text("catalog")
2725
2726    @property
2727    def selects(self) -> t.List[Expression]:
2728        return []
2729
2730    @property
2731    def named_selects(self) -> t.List[str]:
2732        return []
2733
2734    @property
2735    def parts(self) -> t.List[Expression]:
2736        """Return the parts of a table in order catalog, db, table."""
2737        parts: t.List[Expression] = []
2738
2739        for arg in ("catalog", "db", "this"):
2740            part = self.args.get(arg)
2741
2742            if isinstance(part, Dot):
2743                parts.extend(part.flatten())
2744            elif isinstance(part, Expression):
2745                parts.append(part)
2746
2747        return parts
2748
2749    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
2750        parts = self.parts
2751        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
2752        alias = self.args.get("alias")
2753        if alias:
2754            col = alias_(col, alias.this, copy=copy)
2755        return col
2756
2757
2758class Union(Query):
2759    arg_types = {
2760        "with": False,
2761        "this": True,
2762        "expression": True,
2763        "distinct": False,
2764        "by_name": False,
2765        **QUERY_MODIFIERS,
2766    }
2767
2768    def select(
2769        self,
2770        *expressions: t.Optional[ExpOrStr],
2771        append: bool = True,
2772        dialect: DialectType = None,
2773        copy: bool = True,
2774        **opts,
2775    ) -> Union:
2776        this = maybe_copy(self, copy)
2777        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2778        this.expression.unnest().select(
2779            *expressions, append=append, dialect=dialect, copy=False, **opts
2780        )
2781        return this
2782
2783    @property
2784    def named_selects(self) -> t.List[str]:
2785        return self.this.unnest().named_selects
2786
2787    @property
2788    def is_star(self) -> bool:
2789        return self.this.is_star or self.expression.is_star
2790
2791    @property
2792    def selects(self) -> t.List[Expression]:
2793        return self.this.unnest().selects
2794
2795    @property
2796    def left(self) -> Expression:
2797        return self.this
2798
2799    @property
2800    def right(self) -> Expression:
2801        return self.expression
2802
2803
2804class Except(Union):
2805    pass
2806
2807
2808class Intersect(Union):
2809    pass
2810
2811
2812class Unnest(UDTF):
2813    arg_types = {
2814        "expressions": True,
2815        "alias": False,
2816        "offset": False,
2817    }
2818
2819    @property
2820    def selects(self) -> t.List[Expression]:
2821        columns = super().selects
2822        offset = self.args.get("offset")
2823        if offset:
2824            columns = columns + [to_identifier("offset") if offset is True else offset]
2825        return columns
2826
2827
2828class Update(Expression):
2829    arg_types = {
2830        "with": False,
2831        "this": False,
2832        "expressions": True,
2833        "from": False,
2834        "where": False,
2835        "returning": False,
2836        "order": False,
2837        "limit": False,
2838    }
2839
2840
2841class Values(UDTF):
2842    arg_types = {"expressions": True, "alias": False}
2843
2844
2845class Var(Expression):
2846    pass
2847
2848
2849class Version(Expression):
2850    """
2851    Time travel, iceberg, bigquery etc
2852    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2853    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2854    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2855    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2856    this is either TIMESTAMP or VERSION
2857    kind is ("AS OF", "BETWEEN")
2858    """
2859
2860    arg_types = {"this": True, "kind": True, "expression": False}
2861
2862
2863class Schema(Expression):
2864    arg_types = {"this": False, "expressions": False}
2865
2866
2867# https://dev.mysql.com/doc/refman/8.0/en/select.html
2868# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2869class Lock(Expression):
2870    arg_types = {"update": True, "expressions": False, "wait": False}
2871
2872
2873class Select(Query):
2874    arg_types = {
2875        "with": False,
2876        "kind": False,
2877        "expressions": False,
2878        "hint": False,
2879        "distinct": False,
2880        "into": False,
2881        "from": False,
2882        **QUERY_MODIFIERS,
2883    }
2884
2885    def from_(
2886        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2887    ) -> Select:
2888        """
2889        Set the FROM expression.
2890
2891        Example:
2892            >>> Select().from_("tbl").select("x").sql()
2893            'SELECT x FROM tbl'
2894
2895        Args:
2896            expression : the SQL code strings to parse.
2897                If a `From` instance is passed, this is used as-is.
2898                If another `Expression` instance is passed, it will be wrapped in a `From`.
2899            dialect: the dialect used to parse the input expression.
2900            copy: if `False`, modify this expression instance in-place.
2901            opts: other options to use to parse the input expressions.
2902
2903        Returns:
2904            The modified Select expression.
2905        """
2906        return _apply_builder(
2907            expression=expression,
2908            instance=self,
2909            arg="from",
2910            into=From,
2911            prefix="FROM",
2912            dialect=dialect,
2913            copy=copy,
2914            **opts,
2915        )
2916
2917    def group_by(
2918        self,
2919        *expressions: t.Optional[ExpOrStr],
2920        append: bool = True,
2921        dialect: DialectType = None,
2922        copy: bool = True,
2923        **opts,
2924    ) -> Select:
2925        """
2926        Set the GROUP BY expression.
2927
2928        Example:
2929            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2930            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2931
2932        Args:
2933            *expressions: the SQL code strings to parse.
2934                If a `Group` instance is passed, this is used as-is.
2935                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2936                If nothing is passed in then a group by is not applied to the expression
2937            append: if `True`, add to any existing expressions.
2938                Otherwise, this flattens all the `Group` expression into a single expression.
2939            dialect: the dialect used to parse the input expression.
2940            copy: if `False`, modify this expression instance in-place.
2941            opts: other options to use to parse the input expressions.
2942
2943        Returns:
2944            The modified Select expression.
2945        """
2946        if not expressions:
2947            return self if not copy else self.copy()
2948
2949        return _apply_child_list_builder(
2950            *expressions,
2951            instance=self,
2952            arg="group",
2953            append=append,
2954            copy=copy,
2955            prefix="GROUP BY",
2956            into=Group,
2957            dialect=dialect,
2958            **opts,
2959        )
2960
2961    def order_by(
2962        self,
2963        *expressions: t.Optional[ExpOrStr],
2964        append: bool = True,
2965        dialect: DialectType = None,
2966        copy: bool = True,
2967        **opts,
2968    ) -> Select:
2969        """
2970        Set the ORDER BY expression.
2971
2972        Example:
2973            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2974            'SELECT x FROM tbl ORDER BY x DESC'
2975
2976        Args:
2977            *expressions: the SQL code strings to parse.
2978                If a `Group` instance is passed, this is used as-is.
2979                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2980            append: if `True`, add to any existing expressions.
2981                Otherwise, this flattens all the `Order` expression into a single expression.
2982            dialect: the dialect used to parse the input expression.
2983            copy: if `False`, modify this expression instance in-place.
2984            opts: other options to use to parse the input expressions.
2985
2986        Returns:
2987            The modified Select expression.
2988        """
2989        return _apply_child_list_builder(
2990            *expressions,
2991            instance=self,
2992            arg="order",
2993            append=append,
2994            copy=copy,
2995            prefix="ORDER BY",
2996            into=Order,
2997            dialect=dialect,
2998            **opts,
2999        )
3000
3001    def sort_by(
3002        self,
3003        *expressions: t.Optional[ExpOrStr],
3004        append: bool = True,
3005        dialect: DialectType = None,
3006        copy: bool = True,
3007        **opts,
3008    ) -> Select:
3009        """
3010        Set the SORT BY expression.
3011
3012        Example:
3013            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
3014            'SELECT x FROM tbl SORT BY x DESC'
3015
3016        Args:
3017            *expressions: the SQL code strings to parse.
3018                If a `Group` instance is passed, this is used as-is.
3019                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
3020            append: if `True`, add to any existing expressions.
3021                Otherwise, this flattens all the `Order` expression into a single expression.
3022            dialect: the dialect used to parse the input expression.
3023            copy: if `False`, modify this expression instance in-place.
3024            opts: other options to use to parse the input expressions.
3025
3026        Returns:
3027            The modified Select expression.
3028        """
3029        return _apply_child_list_builder(
3030            *expressions,
3031            instance=self,
3032            arg="sort",
3033            append=append,
3034            copy=copy,
3035            prefix="SORT BY",
3036            into=Sort,
3037            dialect=dialect,
3038            **opts,
3039        )
3040
3041    def cluster_by(
3042        self,
3043        *expressions: t.Optional[ExpOrStr],
3044        append: bool = True,
3045        dialect: DialectType = None,
3046        copy: bool = True,
3047        **opts,
3048    ) -> Select:
3049        """
3050        Set the CLUSTER BY expression.
3051
3052        Example:
3053            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
3054            'SELECT x FROM tbl CLUSTER BY x DESC'
3055
3056        Args:
3057            *expressions: the SQL code strings to parse.
3058                If a `Group` instance is passed, this is used as-is.
3059                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
3060            append: if `True`, add to any existing expressions.
3061                Otherwise, this flattens all the `Order` expression into a single expression.
3062            dialect: the dialect used to parse the input expression.
3063            copy: if `False`, modify this expression instance in-place.
3064            opts: other options to use to parse the input expressions.
3065
3066        Returns:
3067            The modified Select expression.
3068        """
3069        return _apply_child_list_builder(
3070            *expressions,
3071            instance=self,
3072            arg="cluster",
3073            append=append,
3074            copy=copy,
3075            prefix="CLUSTER BY",
3076            into=Cluster,
3077            dialect=dialect,
3078            **opts,
3079        )
3080
3081    def limit(
3082        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3083    ) -> Select:
3084        return _apply_builder(
3085            expression=expression,
3086            instance=self,
3087            arg="limit",
3088            into=Limit,
3089            prefix="LIMIT",
3090            dialect=dialect,
3091            copy=copy,
3092            into_arg="expression",
3093            **opts,
3094        )
3095
3096    def offset(
3097        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3098    ) -> Select:
3099        """
3100        Set the OFFSET expression.
3101
3102        Example:
3103            >>> Select().from_("tbl").select("x").offset(10).sql()
3104            'SELECT x FROM tbl OFFSET 10'
3105
3106        Args:
3107            expression: the SQL code string to parse.
3108                This can also be an integer.
3109                If a `Offset` instance is passed, this is used as-is.
3110                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
3111            dialect: the dialect used to parse the input expression.
3112            copy: if `False`, modify this expression instance in-place.
3113            opts: other options to use to parse the input expressions.
3114
3115        Returns:
3116            The modified Select expression.
3117        """
3118        return _apply_builder(
3119            expression=expression,
3120            instance=self,
3121            arg="offset",
3122            into=Offset,
3123            prefix="OFFSET",
3124            dialect=dialect,
3125            copy=copy,
3126            into_arg="expression",
3127            **opts,
3128        )
3129
3130    def select(
3131        self,
3132        *expressions: t.Optional[ExpOrStr],
3133        append: bool = True,
3134        dialect: DialectType = None,
3135        copy: bool = True,
3136        **opts,
3137    ) -> Select:
3138        return _apply_list_builder(
3139            *expressions,
3140            instance=self,
3141            arg="expressions",
3142            append=append,
3143            dialect=dialect,
3144            into=Expression,
3145            copy=copy,
3146            **opts,
3147        )
3148
3149    def lateral(
3150        self,
3151        *expressions: t.Optional[ExpOrStr],
3152        append: bool = True,
3153        dialect: DialectType = None,
3154        copy: bool = True,
3155        **opts,
3156    ) -> Select:
3157        """
3158        Append to or set the LATERAL expressions.
3159
3160        Example:
3161            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3162            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3163
3164        Args:
3165            *expressions: the SQL code strings to parse.
3166                If an `Expression` instance is passed, it will be used as-is.
3167            append: if `True`, add to any existing expressions.
3168                Otherwise, this resets the expressions.
3169            dialect: the dialect used to parse the input expressions.
3170            copy: if `False`, modify this expression instance in-place.
3171            opts: other options to use to parse the input expressions.
3172
3173        Returns:
3174            The modified Select expression.
3175        """
3176        return _apply_list_builder(
3177            *expressions,
3178            instance=self,
3179            arg="laterals",
3180            append=append,
3181            into=Lateral,
3182            prefix="LATERAL VIEW",
3183            dialect=dialect,
3184            copy=copy,
3185            **opts,
3186        )
3187
3188    def join(
3189        self,
3190        expression: ExpOrStr,
3191        on: t.Optional[ExpOrStr] = None,
3192        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3193        append: bool = True,
3194        join_type: t.Optional[str] = None,
3195        join_alias: t.Optional[Identifier | str] = None,
3196        dialect: DialectType = None,
3197        copy: bool = True,
3198        **opts,
3199    ) -> Select:
3200        """
3201        Append to or set the JOIN expressions.
3202
3203        Example:
3204            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3205            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3206
3207            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3208            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3209
3210            Use `join_type` to change the type of join:
3211
3212            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3213            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3214
3215        Args:
3216            expression: the SQL code string to parse.
3217                If an `Expression` instance is passed, it will be used as-is.
3218            on: optionally specify the join "on" criteria as a SQL string.
3219                If an `Expression` instance is passed, it will be used as-is.
3220            using: optionally specify the join "using" criteria as a SQL string.
3221                If an `Expression` instance is passed, it will be used as-is.
3222            append: if `True`, add to any existing expressions.
3223                Otherwise, this resets the expressions.
3224            join_type: if set, alter the parsed join type.
3225            join_alias: an optional alias for the joined source.
3226            dialect: the dialect used to parse the input expressions.
3227            copy: if `False`, modify this expression instance in-place.
3228            opts: other options to use to parse the input expressions.
3229
3230        Returns:
3231            Select: the modified expression.
3232        """
3233        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3234
3235        try:
3236            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3237        except ParseError:
3238            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3239
3240        join = expression if isinstance(expression, Join) else Join(this=expression)
3241
3242        if isinstance(join.this, Select):
3243            join.this.replace(join.this.subquery())
3244
3245        if join_type:
3246            method: t.Optional[Token]
3247            side: t.Optional[Token]
3248            kind: t.Optional[Token]
3249
3250            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3251
3252            if method:
3253                join.set("method", method.text)
3254            if side:
3255                join.set("side", side.text)
3256            if kind:
3257                join.set("kind", kind.text)
3258
3259        if on:
3260            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3261            join.set("on", on)
3262
3263        if using:
3264            join = _apply_list_builder(
3265                *ensure_list(using),
3266                instance=join,
3267                arg="using",
3268                append=append,
3269                copy=copy,
3270                into=Identifier,
3271                **opts,
3272            )
3273
3274        if join_alias:
3275            join.set("this", alias_(join.this, join_alias, table=True))
3276
3277        return _apply_list_builder(
3278            join,
3279            instance=self,
3280            arg="joins",
3281            append=append,
3282            copy=copy,
3283            **opts,
3284        )
3285
3286    def where(
3287        self,
3288        *expressions: t.Optional[ExpOrStr],
3289        append: bool = True,
3290        dialect: DialectType = None,
3291        copy: bool = True,
3292        **opts,
3293    ) -> Select:
3294        """
3295        Append to or set the WHERE expressions.
3296
3297        Example:
3298            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3299            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3300
3301        Args:
3302            *expressions: the SQL code strings to parse.
3303                If an `Expression` instance is passed, it will be used as-is.
3304                Multiple expressions are combined with an AND operator.
3305            append: if `True`, AND the new expressions to any existing expression.
3306                Otherwise, this resets the expression.
3307            dialect: the dialect used to parse the input expressions.
3308            copy: if `False`, modify this expression instance in-place.
3309            opts: other options to use to parse the input expressions.
3310
3311        Returns:
3312            Select: the modified expression.
3313        """
3314        return _apply_conjunction_builder(
3315            *expressions,
3316            instance=self,
3317            arg="where",
3318            append=append,
3319            into=Where,
3320            dialect=dialect,
3321            copy=copy,
3322            **opts,
3323        )
3324
3325    def having(
3326        self,
3327        *expressions: t.Optional[ExpOrStr],
3328        append: bool = True,
3329        dialect: DialectType = None,
3330        copy: bool = True,
3331        **opts,
3332    ) -> Select:
3333        """
3334        Append to or set the HAVING expressions.
3335
3336        Example:
3337            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3338            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3339
3340        Args:
3341            *expressions: the SQL code strings to parse.
3342                If an `Expression` instance is passed, it will be used as-is.
3343                Multiple expressions are combined with an AND operator.
3344            append: if `True`, AND the new expressions to any existing expression.
3345                Otherwise, this resets the expression.
3346            dialect: the dialect used to parse the input expressions.
3347            copy: if `False`, modify this expression instance in-place.
3348            opts: other options to use to parse the input expressions.
3349
3350        Returns:
3351            The modified Select expression.
3352        """
3353        return _apply_conjunction_builder(
3354            *expressions,
3355            instance=self,
3356            arg="having",
3357            append=append,
3358            into=Having,
3359            dialect=dialect,
3360            copy=copy,
3361            **opts,
3362        )
3363
3364    def window(
3365        self,
3366        *expressions: t.Optional[ExpOrStr],
3367        append: bool = True,
3368        dialect: DialectType = None,
3369        copy: bool = True,
3370        **opts,
3371    ) -> Select:
3372        return _apply_list_builder(
3373            *expressions,
3374            instance=self,
3375            arg="windows",
3376            append=append,
3377            into=Window,
3378            dialect=dialect,
3379            copy=copy,
3380            **opts,
3381        )
3382
3383    def qualify(
3384        self,
3385        *expressions: t.Optional[ExpOrStr],
3386        append: bool = True,
3387        dialect: DialectType = None,
3388        copy: bool = True,
3389        **opts,
3390    ) -> Select:
3391        return _apply_conjunction_builder(
3392            *expressions,
3393            instance=self,
3394            arg="qualify",
3395            append=append,
3396            into=Qualify,
3397            dialect=dialect,
3398            copy=copy,
3399            **opts,
3400        )
3401
3402    def distinct(
3403        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3404    ) -> Select:
3405        """
3406        Set the OFFSET expression.
3407
3408        Example:
3409            >>> Select().from_("tbl").select("x").distinct().sql()
3410            'SELECT DISTINCT x FROM tbl'
3411
3412        Args:
3413            ons: the expressions to distinct on
3414            distinct: whether the Select should be distinct
3415            copy: if `False`, modify this expression instance in-place.
3416
3417        Returns:
3418            Select: the modified expression.
3419        """
3420        instance = maybe_copy(self, copy)
3421        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3422        instance.set("distinct", Distinct(on=on) if distinct else None)
3423        return instance
3424
3425    def ctas(
3426        self,
3427        table: ExpOrStr,
3428        properties: t.Optional[t.Dict] = None,
3429        dialect: DialectType = None,
3430        copy: bool = True,
3431        **opts,
3432    ) -> Create:
3433        """
3434        Convert this expression to a CREATE TABLE AS statement.
3435
3436        Example:
3437            >>> Select().select("*").from_("tbl").ctas("x").sql()
3438            'CREATE TABLE x AS SELECT * FROM tbl'
3439
3440        Args:
3441            table: the SQL code string to parse as the table name.
3442                If another `Expression` instance is passed, it will be used as-is.
3443            properties: an optional mapping of table properties
3444            dialect: the dialect used to parse the input table.
3445            copy: if `False`, modify this expression instance in-place.
3446            opts: other options to use to parse the input table.
3447
3448        Returns:
3449            The new Create expression.
3450        """
3451        instance = maybe_copy(self, copy)
3452        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
3453
3454        properties_expression = None
3455        if properties:
3456            properties_expression = Properties.from_dict(properties)
3457
3458        return Create(
3459            this=table_expression,
3460            kind="TABLE",
3461            expression=instance,
3462            properties=properties_expression,
3463        )
3464
3465    def lock(self, update: bool = True, copy: bool = True) -> Select:
3466        """
3467        Set the locking read mode for this expression.
3468
3469        Examples:
3470            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3471            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3472
3473            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3474            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3475
3476        Args:
3477            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3478            copy: if `False`, modify this expression instance in-place.
3479
3480        Returns:
3481            The modified expression.
3482        """
3483        inst = maybe_copy(self, copy)
3484        inst.set("locks", [Lock(update=update)])
3485
3486        return inst
3487
3488    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3489        """
3490        Set hints for this expression.
3491
3492        Examples:
3493            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3494            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3495
3496        Args:
3497            hints: The SQL code strings to parse as the hints.
3498                If an `Expression` instance is passed, it will be used as-is.
3499            dialect: The dialect used to parse the hints.
3500            copy: If `False`, modify this expression instance in-place.
3501
3502        Returns:
3503            The modified expression.
3504        """
3505        inst = maybe_copy(self, copy)
3506        inst.set(
3507            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3508        )
3509
3510        return inst
3511
3512    @property
3513    def named_selects(self) -> t.List[str]:
3514        return [e.output_name for e in self.expressions if e.alias_or_name]
3515
3516    @property
3517    def is_star(self) -> bool:
3518        return any(expression.is_star for expression in self.expressions)
3519
3520    @property
3521    def selects(self) -> t.List[Expression]:
3522        return self.expressions
3523
3524
3525UNWRAPPED_QUERIES = (Select, Union)
3526
3527
3528class Subquery(DerivedTable, Query):
3529    arg_types = {
3530        "this": True,
3531        "alias": False,
3532        "with": False,
3533        **QUERY_MODIFIERS,
3534    }
3535
3536    def unnest(self):
3537        """Returns the first non subquery."""
3538        expression = self
3539        while isinstance(expression, Subquery):
3540            expression = expression.this
3541        return expression
3542
3543    def unwrap(self) -> Subquery:
3544        expression = self
3545        while expression.same_parent and expression.is_wrapper:
3546            expression = t.cast(Subquery, expression.parent)
3547        return expression
3548
3549    def select(
3550        self,
3551        *expressions: t.Optional[ExpOrStr],
3552        append: bool = True,
3553        dialect: DialectType = None,
3554        copy: bool = True,
3555        **opts,
3556    ) -> Subquery:
3557        this = maybe_copy(self, copy)
3558        this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3559        return this
3560
3561    @property
3562    def is_wrapper(self) -> bool:
3563        """
3564        Whether this Subquery acts as a simple wrapper around another expression.
3565
3566        SELECT * FROM (((SELECT * FROM t)))
3567                      ^
3568                      This corresponds to a "wrapper" Subquery node
3569        """
3570        return all(v is None for k, v in self.args.items() if k != "this")
3571
3572    @property
3573    def is_star(self) -> bool:
3574        return self.this.is_star
3575
3576    @property
3577    def output_name(self) -> str:
3578        return self.alias
3579
3580
3581class TableSample(Expression):
3582    arg_types = {
3583        "this": False,
3584        "expressions": False,
3585        "method": False,
3586        "bucket_numerator": False,
3587        "bucket_denominator": False,
3588        "bucket_field": False,
3589        "percent": False,
3590        "rows": False,
3591        "size": False,
3592        "seed": False,
3593    }
3594
3595
3596class Tag(Expression):
3597    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3598
3599    arg_types = {
3600        "this": False,
3601        "prefix": False,
3602        "postfix": False,
3603    }
3604
3605
3606# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3607# https://duckdb.org/docs/sql/statements/pivot
3608class Pivot(Expression):
3609    arg_types = {
3610        "this": False,
3611        "alias": False,
3612        "expressions": False,
3613        "field": False,
3614        "unpivot": False,
3615        "using": False,
3616        "group": False,
3617        "columns": False,
3618        "include_nulls": False,
3619    }
3620
3621    @property
3622    def unpivot(self) -> bool:
3623        return bool(self.args.get("unpivot"))
3624
3625
3626class Window(Condition):
3627    arg_types = {
3628        "this": True,
3629        "partition_by": False,
3630        "order": False,
3631        "spec": False,
3632        "alias": False,
3633        "over": False,
3634        "first": False,
3635    }
3636
3637
3638class WindowSpec(Expression):
3639    arg_types = {
3640        "kind": False,
3641        "start": False,
3642        "start_side": False,
3643        "end": False,
3644        "end_side": False,
3645    }
3646
3647
3648class PreWhere(Expression):
3649    pass
3650
3651
3652class Where(Expression):
3653    pass
3654
3655
3656class Star(Expression):
3657    arg_types = {"except": False, "replace": False}
3658
3659    @property
3660    def name(self) -> str:
3661        return "*"
3662
3663    @property
3664    def output_name(self) -> str:
3665        return self.name
3666
3667
3668class Parameter(Condition):
3669    arg_types = {"this": True, "expression": False}
3670
3671
3672class SessionParameter(Condition):
3673    arg_types = {"this": True, "kind": False}
3674
3675
3676class Placeholder(Condition):
3677    arg_types = {"this": False, "kind": False}
3678
3679
3680class Null(Condition):
3681    arg_types: t.Dict[str, t.Any] = {}
3682
3683    @property
3684    def name(self) -> str:
3685        return "NULL"
3686
3687
3688class Boolean(Condition):
3689    pass
3690
3691
3692class DataTypeParam(Expression):
3693    arg_types = {"this": True, "expression": False}
3694
3695    @property
3696    def name(self) -> str:
3697        return self.this.name
3698
3699
3700class DataType(Expression):
3701    arg_types = {
3702        "this": True,
3703        "expressions": False,
3704        "nested": False,
3705        "values": False,
3706        "prefix": False,
3707        "kind": False,
3708    }
3709
3710    class Type(AutoName):
3711        ARRAY = auto()
3712        AGGREGATEFUNCTION = auto()
3713        SIMPLEAGGREGATEFUNCTION = auto()
3714        BIGDECIMAL = auto()
3715        BIGINT = auto()
3716        BIGSERIAL = auto()
3717        BINARY = auto()
3718        BIT = auto()
3719        BOOLEAN = auto()
3720        BPCHAR = auto()
3721        CHAR = auto()
3722        DATE = auto()
3723        DATE32 = auto()
3724        DATEMULTIRANGE = auto()
3725        DATERANGE = auto()
3726        DATETIME = auto()
3727        DATETIME64 = auto()
3728        DECIMAL = auto()
3729        DOUBLE = auto()
3730        ENUM = auto()
3731        ENUM8 = auto()
3732        ENUM16 = auto()
3733        FIXEDSTRING = auto()
3734        FLOAT = auto()
3735        GEOGRAPHY = auto()
3736        GEOMETRY = auto()
3737        HLLSKETCH = auto()
3738        HSTORE = auto()
3739        IMAGE = auto()
3740        INET = auto()
3741        INT = auto()
3742        INT128 = auto()
3743        INT256 = auto()
3744        INT4MULTIRANGE = auto()
3745        INT4RANGE = auto()
3746        INT8MULTIRANGE = auto()
3747        INT8RANGE = auto()
3748        INTERVAL = auto()
3749        IPADDRESS = auto()
3750        IPPREFIX = auto()
3751        IPV4 = auto()
3752        IPV6 = auto()
3753        JSON = auto()
3754        JSONB = auto()
3755        LONGBLOB = auto()
3756        LONGTEXT = auto()
3757        LOWCARDINALITY = auto()
3758        MAP = auto()
3759        MEDIUMBLOB = auto()
3760        MEDIUMINT = auto()
3761        MEDIUMTEXT = auto()
3762        MONEY = auto()
3763        NCHAR = auto()
3764        NESTED = auto()
3765        NULL = auto()
3766        NULLABLE = auto()
3767        NUMMULTIRANGE = auto()
3768        NUMRANGE = auto()
3769        NVARCHAR = auto()
3770        OBJECT = auto()
3771        ROWVERSION = auto()
3772        SERIAL = auto()
3773        SET = auto()
3774        SMALLINT = auto()
3775        SMALLMONEY = auto()
3776        SMALLSERIAL = auto()
3777        STRUCT = auto()
3778        SUPER = auto()
3779        TEXT = auto()
3780        TINYBLOB = auto()
3781        TINYTEXT = auto()
3782        TIME = auto()
3783        TIMETZ = auto()
3784        TIMESTAMP = auto()
3785        TIMESTAMPLTZ = auto()
3786        TIMESTAMPTZ = auto()
3787        TIMESTAMP_S = auto()
3788        TIMESTAMP_MS = auto()
3789        TIMESTAMP_NS = auto()
3790        TINYINT = auto()
3791        TSMULTIRANGE = auto()
3792        TSRANGE = auto()
3793        TSTZMULTIRANGE = auto()
3794        TSTZRANGE = auto()
3795        UBIGINT = auto()
3796        UINT = auto()
3797        UINT128 = auto()
3798        UINT256 = auto()
3799        UMEDIUMINT = auto()
3800        UDECIMAL = auto()
3801        UNIQUEIDENTIFIER = auto()
3802        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3803        USERDEFINED = "USER-DEFINED"
3804        USMALLINT = auto()
3805        UTINYINT = auto()
3806        UUID = auto()
3807        VARBINARY = auto()
3808        VARCHAR = auto()
3809        VARIANT = auto()
3810        XML = auto()
3811        YEAR = auto()
3812
3813    TEXT_TYPES = {
3814        Type.CHAR,
3815        Type.NCHAR,
3816        Type.NVARCHAR,
3817        Type.TEXT,
3818        Type.VARCHAR,
3819    }
3820
3821    INTEGER_TYPES = {
3822        Type.BIGINT,
3823        Type.BIT,
3824        Type.INT,
3825        Type.INT128,
3826        Type.INT256,
3827        Type.MEDIUMINT,
3828        Type.SMALLINT,
3829        Type.TINYINT,
3830        Type.UBIGINT,
3831        Type.UINT,
3832        Type.UINT128,
3833        Type.UINT256,
3834        Type.UMEDIUMINT,
3835        Type.USMALLINT,
3836        Type.UTINYINT,
3837    }
3838
3839    FLOAT_TYPES = {
3840        Type.DOUBLE,
3841        Type.FLOAT,
3842    }
3843
3844    REAL_TYPES = {
3845        *FLOAT_TYPES,
3846        Type.BIGDECIMAL,
3847        Type.DECIMAL,
3848        Type.MONEY,
3849        Type.SMALLMONEY,
3850        Type.UDECIMAL,
3851    }
3852
3853    NUMERIC_TYPES = {
3854        *INTEGER_TYPES,
3855        *REAL_TYPES,
3856    }
3857
3858    TEMPORAL_TYPES = {
3859        Type.DATE,
3860        Type.DATE32,
3861        Type.DATETIME,
3862        Type.DATETIME64,
3863        Type.TIME,
3864        Type.TIMESTAMP,
3865        Type.TIMESTAMPLTZ,
3866        Type.TIMESTAMPTZ,
3867        Type.TIMESTAMP_MS,
3868        Type.TIMESTAMP_NS,
3869        Type.TIMESTAMP_S,
3870        Type.TIMETZ,
3871    }
3872
3873    @classmethod
3874    def build(
3875        cls,
3876        dtype: DATA_TYPE,
3877        dialect: DialectType = None,
3878        udt: bool = False,
3879        copy: bool = True,
3880        **kwargs,
3881    ) -> DataType:
3882        """
3883        Constructs a DataType object.
3884
3885        Args:
3886            dtype: the data type of interest.
3887            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3888            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3889                DataType, thus creating a user-defined type.
3890            copy: whether to copy the data type.
3891            kwargs: additional arguments to pass in the constructor of DataType.
3892
3893        Returns:
3894            The constructed DataType object.
3895        """
3896        from sqlglot import parse_one
3897
3898        if isinstance(dtype, str):
3899            if dtype.upper() == "UNKNOWN":
3900                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3901
3902            try:
3903                data_type_exp = parse_one(
3904                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3905                )
3906            except ParseError:
3907                if udt:
3908                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3909                raise
3910        elif isinstance(dtype, DataType.Type):
3911            data_type_exp = DataType(this=dtype)
3912        elif isinstance(dtype, DataType):
3913            return maybe_copy(dtype, copy)
3914        else:
3915            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3916
3917        return DataType(**{**data_type_exp.args, **kwargs})
3918
3919    def is_type(self, *dtypes: DATA_TYPE) -> bool:
3920        """
3921        Checks whether this DataType matches one of the provided data types. Nested types or precision
3922        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3923
3924        Args:
3925            dtypes: the data types to compare this DataType to.
3926
3927        Returns:
3928            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3929        """
3930        for dtype in dtypes:
3931            other = DataType.build(dtype, copy=False, udt=True)
3932
3933            if (
3934                other.expressions
3935                or self.this == DataType.Type.USERDEFINED
3936                or other.this == DataType.Type.USERDEFINED
3937            ):
3938                matches = self == other
3939            else:
3940                matches = self.this == other.this
3941
3942            if matches:
3943                return True
3944        return False
3945
3946
3947DATA_TYPE = t.Union[str, DataType, DataType.Type]
3948
3949
3950# https://www.postgresql.org/docs/15/datatype-pseudo.html
3951class PseudoType(DataType):
3952    arg_types = {"this": True}
3953
3954
3955# https://www.postgresql.org/docs/15/datatype-oid.html
3956class ObjectIdentifier(DataType):
3957    arg_types = {"this": True}
3958
3959
3960# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3961class SubqueryPredicate(Predicate):
3962    pass
3963
3964
3965class All(SubqueryPredicate):
3966    pass
3967
3968
3969class Any(SubqueryPredicate):
3970    pass
3971
3972
3973class Exists(SubqueryPredicate):
3974    pass
3975
3976
3977# Commands to interact with the databases or engines. For most of the command
3978# expressions we parse whatever comes after the command's name as a string.
3979class Command(Expression):
3980    arg_types = {"this": True, "expression": False}
3981
3982
3983class Transaction(Expression):
3984    arg_types = {"this": False, "modes": False, "mark": False}
3985
3986
3987class Commit(Expression):
3988    arg_types = {"chain": False, "this": False, "durability": False}
3989
3990
3991class Rollback(Expression):
3992    arg_types = {"savepoint": False, "this": False}
3993
3994
3995class AlterTable(Expression):
3996    arg_types = {
3997        "this": True,
3998        "actions": True,
3999        "exists": False,
4000        "only": False,
4001        "options": False,
4002    }
4003
4004
4005class AddConstraint(Expression):
4006    arg_types = {"expressions": True}
4007
4008
4009class DropPartition(Expression):
4010    arg_types = {"expressions": True, "exists": False}
4011
4012
4013# Binary expressions like (ADD a b)
4014class Binary(Condition):
4015    arg_types = {"this": True, "expression": True}
4016
4017    @property
4018    def left(self) -> Expression:
4019        return self.this
4020
4021    @property
4022    def right(self) -> Expression:
4023        return self.expression
4024
4025
4026class Add(Binary):
4027    pass
4028
4029
4030class Connector(Binary):
4031    pass
4032
4033
4034class And(Connector):
4035    pass
4036
4037
4038class Or(Connector):
4039    pass
4040
4041
4042class BitwiseAnd(Binary):
4043    pass
4044
4045
4046class BitwiseLeftShift(Binary):
4047    pass
4048
4049
4050class BitwiseOr(Binary):
4051    pass
4052
4053
4054class BitwiseRightShift(Binary):
4055    pass
4056
4057
4058class BitwiseXor(Binary):
4059    pass
4060
4061
4062class Div(Binary):
4063    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
4064
4065
4066class Overlaps(Binary):
4067    pass
4068
4069
4070class Dot(Binary):
4071    @property
4072    def is_star(self) -> bool:
4073        return self.expression.is_star
4074
4075    @property
4076    def name(self) -> str:
4077        return self.expression.name
4078
4079    @property
4080    def output_name(self) -> str:
4081        return self.name
4082
4083    @classmethod
4084    def build(self, expressions: t.Sequence[Expression]) -> Dot:
4085        """Build a Dot object with a sequence of expressions."""
4086        if len(expressions) < 2:
4087            raise ValueError("Dot requires >= 2 expressions.")
4088
4089        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
4090
4091    @property
4092    def parts(self) -> t.List[Expression]:
4093        """Return the parts of a table / column in order catalog, db, table."""
4094        this, *parts = self.flatten()
4095
4096        parts.reverse()
4097
4098        for arg in ("this", "table", "db", "catalog"):
4099            part = this.args.get(arg)
4100
4101            if isinstance(part, Expression):
4102                parts.append(part)
4103
4104        parts.reverse()
4105        return parts
4106
4107
4108class DPipe(Binary):
4109    arg_types = {"this": True, "expression": True, "safe": False}
4110
4111
4112class EQ(Binary, Predicate):
4113    pass
4114
4115
4116class NullSafeEQ(Binary, Predicate):
4117    pass
4118
4119
4120class NullSafeNEQ(Binary, Predicate):
4121    pass
4122
4123
4124# Represents e.g. := in DuckDB which is mostly used for setting parameters
4125class PropertyEQ(Binary):
4126    pass
4127
4128
4129class Distance(Binary):
4130    pass
4131
4132
4133class Escape(Binary):
4134    pass
4135
4136
4137class Glob(Binary, Predicate):
4138    pass
4139
4140
4141class GT(Binary, Predicate):
4142    pass
4143
4144
4145class GTE(Binary, Predicate):
4146    pass
4147
4148
4149class ILike(Binary, Predicate):
4150    pass
4151
4152
4153class ILikeAny(Binary, Predicate):
4154    pass
4155
4156
4157class IntDiv(Binary):
4158    pass
4159
4160
4161class Is(Binary, Predicate):
4162    pass
4163
4164
4165class Kwarg(Binary):
4166    """Kwarg in special functions like func(kwarg => y)."""
4167
4168
4169class Like(Binary, Predicate):
4170    pass
4171
4172
4173class LikeAny(Binary, Predicate):
4174    pass
4175
4176
4177class LT(Binary, Predicate):
4178    pass
4179
4180
4181class LTE(Binary, Predicate):
4182    pass
4183
4184
4185class Mod(Binary):
4186    pass
4187
4188
4189class Mul(Binary):
4190    pass
4191
4192
4193class NEQ(Binary, Predicate):
4194    pass
4195
4196
4197# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4198class Operator(Binary):
4199    arg_types = {"this": True, "operator": True, "expression": True}
4200
4201
4202class SimilarTo(Binary, Predicate):
4203    pass
4204
4205
4206class Slice(Binary):
4207    arg_types = {"this": False, "expression": False}
4208
4209
4210class Sub(Binary):
4211    pass
4212
4213
4214# Unary Expressions
4215# (NOT a)
4216class Unary(Condition):
4217    pass
4218
4219
4220class BitwiseNot(Unary):
4221    pass
4222
4223
4224class Not(Unary):
4225    pass
4226
4227
4228class Paren(Unary):
4229    arg_types = {"this": True, "with": False}
4230
4231    @property
4232    def output_name(self) -> str:
4233        return self.this.name
4234
4235
4236class Neg(Unary):
4237    pass
4238
4239
4240class Alias(Expression):
4241    arg_types = {"this": True, "alias": False}
4242
4243    @property
4244    def output_name(self) -> str:
4245        return self.alias
4246
4247
4248# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4249# other dialects require identifiers. This enables us to transpile between them easily.
4250class PivotAlias(Alias):
4251    pass
4252
4253
4254class Aliases(Expression):
4255    arg_types = {"this": True, "expressions": True}
4256
4257    @property
4258    def aliases(self):
4259        return self.expressions
4260
4261
4262# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4263class AtIndex(Expression):
4264    arg_types = {"this": True, "expression": True}
4265
4266
4267class AtTimeZone(Expression):
4268    arg_types = {"this": True, "zone": True}
4269
4270
4271class FromTimeZone(Expression):
4272    arg_types = {"this": True, "zone": True}
4273
4274
4275class Between(Predicate):
4276    arg_types = {"this": True, "low": True, "high": True}
4277
4278
4279class Bracket(Condition):
4280    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4281    arg_types = {"this": True, "expressions": True, "offset": False, "safe": False}
4282
4283    @property
4284    def output_name(self) -> str:
4285        if len(self.expressions) == 1:
4286            return self.expressions[0].output_name
4287
4288        return super().output_name
4289
4290
4291class Distinct(Expression):
4292    arg_types = {"expressions": False, "on": False}
4293
4294
4295class In(Predicate):
4296    arg_types = {
4297        "this": True,
4298        "expressions": False,
4299        "query": False,
4300        "unnest": False,
4301        "field": False,
4302        "is_global": False,
4303    }
4304
4305
4306# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4307class ForIn(Expression):
4308    arg_types = {"this": True, "expression": True}
4309
4310
4311class TimeUnit(Expression):
4312    """Automatically converts unit arg into a var."""
4313
4314    arg_types = {"unit": False}
4315
4316    UNABBREVIATED_UNIT_NAME = {
4317        "D": "DAY",
4318        "H": "HOUR",
4319        "M": "MINUTE",
4320        "MS": "MILLISECOND",
4321        "NS": "NANOSECOND",
4322        "Q": "QUARTER",
4323        "S": "SECOND",
4324        "US": "MICROSECOND",
4325        "W": "WEEK",
4326        "Y": "YEAR",
4327    }
4328
4329    VAR_LIKE = (Column, Literal, Var)
4330
4331    def __init__(self, **args):
4332        unit = args.get("unit")
4333        if isinstance(unit, self.VAR_LIKE):
4334            args["unit"] = Var(
4335                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4336            )
4337        elif isinstance(unit, Week):
4338            unit.set("this", Var(this=unit.this.name.upper()))
4339
4340        super().__init__(**args)
4341
4342    @property
4343    def unit(self) -> t.Optional[Var]:
4344        return self.args.get("unit")
4345
4346
4347class IntervalOp(TimeUnit):
4348    arg_types = {"unit": True, "expression": True}
4349
4350    def interval(self):
4351        return Interval(
4352            this=self.expression.copy(),
4353            unit=self.unit.copy(),
4354        )
4355
4356
4357# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4358# https://trino.io/docs/current/language/types.html#interval-day-to-second
4359# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4360class IntervalSpan(DataType):
4361    arg_types = {"this": True, "expression": True}
4362
4363
4364class Interval(TimeUnit):
4365    arg_types = {"this": False, "unit": False}
4366
4367
4368class IgnoreNulls(Expression):
4369    pass
4370
4371
4372class RespectNulls(Expression):
4373    pass
4374
4375
4376# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
4377class HavingMax(Expression):
4378    arg_types = {"this": True, "expression": True, "max": True}
4379
4380
4381# Functions
4382class Func(Condition):
4383    """
4384    The base class for all function expressions.
4385
4386    Attributes:
4387        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4388            treated as a variable length argument and the argument's value will be stored as a list.
4389        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
4390            function expression. These values are used to map this node to a name during parsing as
4391            well as to provide the function's name during SQL string generation. By default the SQL
4392            name is set to the expression's class name transformed to snake case.
4393    """
4394
4395    is_var_len_args = False
4396
4397    @classmethod
4398    def from_arg_list(cls, args):
4399        if cls.is_var_len_args:
4400            all_arg_keys = list(cls.arg_types)
4401            # If this function supports variable length argument treat the last argument as such.
4402            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4403            num_non_var = len(non_var_len_arg_keys)
4404
4405            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4406            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4407        else:
4408            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4409
4410        return cls(**args_dict)
4411
4412    @classmethod
4413    def sql_names(cls):
4414        if cls is Func:
4415            raise NotImplementedError(
4416                "SQL name is only supported by concrete function implementations"
4417            )
4418        if "_sql_names" not in cls.__dict__:
4419            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4420        return cls._sql_names
4421
4422    @classmethod
4423    def sql_name(cls):
4424        return cls.sql_names()[0]
4425
4426    @classmethod
4427    def default_parser_mappings(cls):
4428        return {name: cls.from_arg_list for name in cls.sql_names()}
4429
4430
4431class AggFunc(Func):
4432    pass
4433
4434
4435class ParameterizedAgg(AggFunc):
4436    arg_types = {"this": True, "expressions": True, "params": True}
4437
4438
4439class Abs(Func):
4440    pass
4441
4442
4443class ArgMax(AggFunc):
4444    arg_types = {"this": True, "expression": True, "count": False}
4445    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4446
4447
4448class ArgMin(AggFunc):
4449    arg_types = {"this": True, "expression": True, "count": False}
4450    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4451
4452
4453class ApproxTopK(AggFunc):
4454    arg_types = {"this": True, "expression": False, "counters": False}
4455
4456
4457class Flatten(Func):
4458    pass
4459
4460
4461# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4462class Transform(Func):
4463    arg_types = {"this": True, "expression": True}
4464
4465
4466class Anonymous(Func):
4467    arg_types = {"this": True, "expressions": False}
4468    is_var_len_args = True
4469
4470    @property
4471    def name(self) -> str:
4472        return self.this if isinstance(self.this, str) else self.this.name
4473
4474
4475class AnonymousAggFunc(AggFunc):
4476    arg_types = {"this": True, "expressions": False}
4477    is_var_len_args = True
4478
4479
4480# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4481class CombinedAggFunc(AnonymousAggFunc):
4482    arg_types = {"this": True, "expressions": False, "parts": True}
4483
4484
4485class CombinedParameterizedAgg(ParameterizedAgg):
4486    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4487
4488
4489# https://docs.snowflake.com/en/sql-reference/functions/hll
4490# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4491class Hll(AggFunc):
4492    arg_types = {"this": True, "expressions": False}
4493    is_var_len_args = True
4494
4495
4496class ApproxDistinct(AggFunc):
4497    arg_types = {"this": True, "accuracy": False}
4498    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4499
4500
4501class Array(Func):
4502    arg_types = {"expressions": False}
4503    is_var_len_args = True
4504
4505
4506# https://docs.snowflake.com/en/sql-reference/functions/to_array
4507class ToArray(Func):
4508    pass
4509
4510
4511# https://docs.snowflake.com/en/sql-reference/functions/to_char
4512# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4513class ToChar(Func):
4514    arg_types = {"this": True, "format": False, "nlsparam": False}
4515
4516
4517# https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#syntax
4518class Convert(Func):
4519    arg_types = {"this": True, "expression": True, "style": False}
4520
4521
4522class GenerateSeries(Func):
4523    arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
4524
4525
4526class ArrayAgg(AggFunc):
4527    pass
4528
4529
4530class ArrayUniqueAgg(AggFunc):
4531    pass
4532
4533
4534class ArrayAll(Func):
4535    arg_types = {"this": True, "expression": True}
4536
4537
4538# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
4539class ArrayAny(Func):
4540    arg_types = {"this": True, "expression": True}
4541
4542
4543class ArrayConcat(Func):
4544    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4545    arg_types = {"this": True, "expressions": False}
4546    is_var_len_args = True
4547
4548
4549class ArrayContains(Binary, Func):
4550    pass
4551
4552
4553class ArrayContained(Binary):
4554    pass
4555
4556
4557class ArrayFilter(Func):
4558    arg_types = {"this": True, "expression": True}
4559    _sql_names = ["FILTER", "ARRAY_FILTER"]
4560
4561
4562class ArrayJoin(Func):
4563    arg_types = {"this": True, "expression": True, "null": False}
4564    _sql_names = ["ARRAY_JOIN", "ARRAY_TO_STRING"]
4565
4566
4567class ArrayOverlaps(Binary, Func):
4568    pass
4569
4570
4571class ArraySize(Func):
4572    arg_types = {"this": True, "expression": False}
4573    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
4574
4575
4576class ArraySort(Func):
4577    arg_types = {"this": True, "expression": False}
4578
4579
4580class ArraySum(Func):
4581    arg_types = {"this": True, "expression": False}
4582
4583
4584class ArrayUnionAgg(AggFunc):
4585    pass
4586
4587
4588class Avg(AggFunc):
4589    pass
4590
4591
4592class AnyValue(AggFunc):
4593    pass
4594
4595
4596class Lag(AggFunc):
4597    arg_types = {"this": True, "offset": False, "default": False}
4598
4599
4600class Lead(AggFunc):
4601    arg_types = {"this": True, "offset": False, "default": False}
4602
4603
4604# some dialects have a distinction between first and first_value, usually first is an aggregate func
4605# and first_value is a window func
4606class First(AggFunc):
4607    pass
4608
4609
4610class Last(AggFunc):
4611    pass
4612
4613
4614class FirstValue(AggFunc):
4615    pass
4616
4617
4618class LastValue(AggFunc):
4619    pass
4620
4621
4622class NthValue(AggFunc):
4623    arg_types = {"this": True, "offset": True}
4624
4625
4626class Case(Func):
4627    arg_types = {"this": False, "ifs": True, "default": False}
4628
4629    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4630        instance = maybe_copy(self, copy)
4631        instance.append(
4632            "ifs",
4633            If(
4634                this=maybe_parse(condition, copy=copy, **opts),
4635                true=maybe_parse(then, copy=copy, **opts),
4636            ),
4637        )
4638        return instance
4639
4640    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4641        instance = maybe_copy(self, copy)
4642        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4643        return instance
4644
4645
4646class Cast(Func):
4647    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4648
4649    @property
4650    def name(self) -> str:
4651        return self.this.name
4652
4653    @property
4654    def to(self) -> DataType:
4655        return self.args["to"]
4656
4657    @property
4658    def output_name(self) -> str:
4659        return self.name
4660
4661    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4662        """
4663        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4664        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4665        array<int> != array<float>.
4666
4667        Args:
4668            dtypes: the data types to compare this Cast's DataType to.
4669
4670        Returns:
4671            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4672        """
4673        return self.to.is_type(*dtypes)
4674
4675
4676class TryCast(Cast):
4677    pass
4678
4679
4680class CastToStrType(Func):
4681    arg_types = {"this": True, "to": True}
4682
4683
4684class Collate(Binary, Func):
4685    pass
4686
4687
4688class Ceil(Func):
4689    arg_types = {"this": True, "decimals": False}
4690    _sql_names = ["CEIL", "CEILING"]
4691
4692
4693class Coalesce(Func):
4694    arg_types = {"this": True, "expressions": False}
4695    is_var_len_args = True
4696    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4697
4698
4699class Chr(Func):
4700    arg_types = {"this": True, "charset": False, "expressions": False}
4701    is_var_len_args = True
4702    _sql_names = ["CHR", "CHAR"]
4703
4704
4705class Concat(Func):
4706    arg_types = {"expressions": True, "safe": False, "coalesce": False}
4707    is_var_len_args = True
4708
4709
4710class ConcatWs(Concat):
4711    _sql_names = ["CONCAT_WS"]
4712
4713
4714# https://docs.oracle.com/cd/B13789_01/server.101/b10759/operators004.htm#i1035022
4715class ConnectByRoot(Func):
4716    pass
4717
4718
4719class Count(AggFunc):
4720    arg_types = {"this": False, "expressions": False}
4721    is_var_len_args = True
4722
4723
4724class CountIf(AggFunc):
4725    _sql_names = ["COUNT_IF", "COUNTIF"]
4726
4727
4728# cube root
4729class Cbrt(Func):
4730    pass
4731
4732
4733class CurrentDate(Func):
4734    arg_types = {"this": False}
4735
4736
4737class CurrentDatetime(Func):
4738    arg_types = {"this": False}
4739
4740
4741class CurrentTime(Func):
4742    arg_types = {"this": False}
4743
4744
4745class CurrentTimestamp(Func):
4746    arg_types = {"this": False, "transaction": False}
4747
4748
4749class CurrentUser(Func):
4750    arg_types = {"this": False}
4751
4752
4753class DateAdd(Func, IntervalOp):
4754    arg_types = {"this": True, "expression": True, "unit": False}
4755
4756
4757class DateSub(Func, IntervalOp):
4758    arg_types = {"this": True, "expression": True, "unit": False}
4759
4760
4761class DateDiff(Func, TimeUnit):
4762    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4763    arg_types = {"this": True, "expression": True, "unit": False}
4764
4765
4766class DateTrunc(Func):
4767    arg_types = {"unit": True, "this": True, "zone": False}
4768
4769    def __init__(self, **args):
4770        unit = args.get("unit")
4771        if isinstance(unit, TimeUnit.VAR_LIKE):
4772            args["unit"] = Literal.string(
4773                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4774            )
4775        elif isinstance(unit, Week):
4776            unit.set("this", Literal.string(unit.this.name.upper()))
4777
4778        super().__init__(**args)
4779
4780    @property
4781    def unit(self) -> Expression:
4782        return self.args["unit"]
4783
4784
4785class DatetimeAdd(Func, IntervalOp):
4786    arg_types = {"this": True, "expression": True, "unit": False}
4787
4788
4789class DatetimeSub(Func, IntervalOp):
4790    arg_types = {"this": True, "expression": True, "unit": False}
4791
4792
4793class DatetimeDiff(Func, TimeUnit):
4794    arg_types = {"this": True, "expression": True, "unit": False}
4795
4796
4797class DatetimeTrunc(Func, TimeUnit):
4798    arg_types = {"this": True, "unit": True, "zone": False}
4799
4800
4801class DayOfWeek(Func):
4802    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4803
4804
4805class DayOfMonth(Func):
4806    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4807
4808
4809class DayOfYear(Func):
4810    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4811
4812
4813class ToDays(Func):
4814    pass
4815
4816
4817class WeekOfYear(Func):
4818    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4819
4820
4821class MonthsBetween(Func):
4822    arg_types = {"this": True, "expression": True, "roundoff": False}
4823
4824
4825class LastDay(Func, TimeUnit):
4826    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
4827    arg_types = {"this": True, "unit": False}
4828
4829
4830class Extract(Func):
4831    arg_types = {"this": True, "expression": True}
4832
4833
4834class Timestamp(Func):
4835    arg_types = {"this": False, "expression": False, "with_tz": False}
4836
4837
4838class TimestampAdd(Func, TimeUnit):
4839    arg_types = {"this": True, "expression": True, "unit": False}
4840
4841
4842class TimestampSub(Func, TimeUnit):
4843    arg_types = {"this": True, "expression": True, "unit": False}
4844
4845
4846class TimestampDiff(Func, TimeUnit):
4847    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
4848    arg_types = {"this": True, "expression": True, "unit": False}
4849
4850
4851class TimestampTrunc(Func, TimeUnit):
4852    arg_types = {"this": True, "unit": True, "zone": False}
4853
4854
4855class TimeAdd(Func, TimeUnit):
4856    arg_types = {"this": True, "expression": True, "unit": False}
4857
4858
4859class TimeSub(Func, TimeUnit):
4860    arg_types = {"this": True, "expression": True, "unit": False}
4861
4862
4863class TimeDiff(Func, TimeUnit):
4864    arg_types = {"this": True, "expression": True, "unit": False}
4865
4866
4867class TimeTrunc(Func, TimeUnit):
4868    arg_types = {"this": True, "unit": True, "zone": False}
4869
4870
4871class DateFromParts(Func):
4872    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
4873    arg_types = {"year": True, "month": True, "day": True}
4874
4875
4876class TimeFromParts(Func):
4877    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
4878    arg_types = {
4879        "hour": True,
4880        "min": True,
4881        "sec": True,
4882        "nano": False,
4883        "fractions": False,
4884        "precision": False,
4885    }
4886
4887
4888class DateStrToDate(Func):
4889    pass
4890
4891
4892class DateToDateStr(Func):
4893    pass
4894
4895
4896class DateToDi(Func):
4897    pass
4898
4899
4900# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4901class Date(Func):
4902    arg_types = {"this": False, "zone": False, "expressions": False}
4903    is_var_len_args = True
4904
4905
4906class Day(Func):
4907    pass
4908
4909
4910class Decode(Func):
4911    arg_types = {"this": True, "charset": True, "replace": False}
4912
4913
4914class DiToDate(Func):
4915    pass
4916
4917
4918class Encode(Func):
4919    arg_types = {"this": True, "charset": True}
4920
4921
4922class Exp(Func):
4923    pass
4924
4925
4926# https://docs.snowflake.com/en/sql-reference/functions/flatten
4927class Explode(Func):
4928    arg_types = {"this": True, "expressions": False}
4929    is_var_len_args = True
4930
4931
4932class ExplodeOuter(Explode):
4933    pass
4934
4935
4936class Posexplode(Explode):
4937    pass
4938
4939
4940class PosexplodeOuter(Posexplode, ExplodeOuter):
4941    pass
4942
4943
4944class Floor(Func):
4945    arg_types = {"this": True, "decimals": False}
4946
4947
4948class FromBase64(Func):
4949    pass
4950
4951
4952class ToBase64(Func):
4953    pass
4954
4955
4956class Greatest(Func):
4957    arg_types = {"this": True, "expressions": False}
4958    is_var_len_args = True
4959
4960
4961class GroupConcat(AggFunc):
4962    arg_types = {"this": True, "separator": False}
4963
4964
4965class Hex(Func):
4966    pass
4967
4968
4969class Xor(Connector, Func):
4970    arg_types = {"this": False, "expression": False, "expressions": False}
4971
4972
4973class If(Func):
4974    arg_types = {"this": True, "true": True, "false": False}
4975    _sql_names = ["IF", "IIF"]
4976
4977
4978class Nullif(Func):
4979    arg_types = {"this": True, "expression": True}
4980
4981
4982class Initcap(Func):
4983    arg_types = {"this": True, "expression": False}
4984
4985
4986class IsNan(Func):
4987    _sql_names = ["IS_NAN", "ISNAN"]
4988
4989
4990class IsInf(Func):
4991    _sql_names = ["IS_INF", "ISINF"]
4992
4993
4994class JSONPath(Expression):
4995    arg_types = {"expressions": True}
4996
4997    @property
4998    def output_name(self) -> str:
4999        last_segment = self.expressions[-1].this
5000        return last_segment if isinstance(last_segment, str) else ""
5001
5002
5003class JSONPathPart(Expression):
5004    arg_types = {}
5005
5006
5007class JSONPathFilter(JSONPathPart):
5008    arg_types = {"this": True}
5009
5010
5011class JSONPathKey(JSONPathPart):
5012    arg_types = {"this": True}
5013
5014
5015class JSONPathRecursive(JSONPathPart):
5016    arg_types = {"this": False}
5017
5018
5019class JSONPathRoot(JSONPathPart):
5020    pass
5021
5022
5023class JSONPathScript(JSONPathPart):
5024    arg_types = {"this": True}
5025
5026
5027class JSONPathSlice(JSONPathPart):
5028    arg_types = {"start": False, "end": False, "step": False}
5029
5030
5031class JSONPathSelector(JSONPathPart):
5032    arg_types = {"this": True}
5033
5034
5035class JSONPathSubscript(JSONPathPart):
5036    arg_types = {"this": True}
5037
5038
5039class JSONPathUnion(JSONPathPart):
5040    arg_types = {"expressions": True}
5041
5042
5043class JSONPathWildcard(JSONPathPart):
5044    pass
5045
5046
5047class FormatJson(Expression):
5048    pass
5049
5050
5051class JSONKeyValue(Expression):
5052    arg_types = {"this": True, "expression": True}
5053
5054
5055class JSONObject(Func):
5056    arg_types = {
5057        "expressions": False,
5058        "null_handling": False,
5059        "unique_keys": False,
5060        "return_type": False,
5061        "encoding": False,
5062    }
5063
5064
5065class JSONObjectAgg(AggFunc):
5066    arg_types = {
5067        "expressions": False,
5068        "null_handling": False,
5069        "unique_keys": False,
5070        "return_type": False,
5071        "encoding": False,
5072    }
5073
5074
5075# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
5076class JSONArray(Func):
5077    arg_types = {
5078        "expressions": True,
5079        "null_handling": False,
5080        "return_type": False,
5081        "strict": False,
5082    }
5083
5084
5085# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
5086class JSONArrayAgg(Func):
5087    arg_types = {
5088        "this": True,
5089        "order": False,
5090        "null_handling": False,
5091        "return_type": False,
5092        "strict": False,
5093    }
5094
5095
5096# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5097# Note: parsing of JSON column definitions is currently incomplete.
5098class JSONColumnDef(Expression):
5099    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
5100
5101
5102class JSONSchema(Expression):
5103    arg_types = {"expressions": True}
5104
5105
5106# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5107class JSONTable(Func):
5108    arg_types = {
5109        "this": True,
5110        "schema": True,
5111        "path": False,
5112        "error_handling": False,
5113        "empty_handling": False,
5114    }
5115
5116
5117class OpenJSONColumnDef(Expression):
5118    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
5119
5120
5121class OpenJSON(Func):
5122    arg_types = {"this": True, "path": False, "expressions": False}
5123
5124
5125class JSONBContains(Binary):
5126    _sql_names = ["JSONB_CONTAINS"]
5127
5128
5129class JSONExtract(Binary, Func):
5130    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5131    _sql_names = ["JSON_EXTRACT"]
5132    is_var_len_args = True
5133
5134    @property
5135    def output_name(self) -> str:
5136        return self.expression.output_name if not self.expressions else ""
5137
5138
5139class JSONExtractScalar(Binary, Func):
5140    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5141    _sql_names = ["JSON_EXTRACT_SCALAR"]
5142    is_var_len_args = True
5143
5144    @property
5145    def output_name(self) -> str:
5146        return self.expression.output_name
5147
5148
5149class JSONBExtract(Binary, Func):
5150    _sql_names = ["JSONB_EXTRACT"]
5151
5152
5153class JSONBExtractScalar(Binary, Func):
5154    _sql_names = ["JSONB_EXTRACT_SCALAR"]
5155
5156
5157class JSONFormat(Func):
5158    arg_types = {"this": False, "options": False}
5159    _sql_names = ["JSON_FORMAT"]
5160
5161
5162# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
5163class JSONArrayContains(Binary, Predicate, Func):
5164    _sql_names = ["JSON_ARRAY_CONTAINS"]
5165
5166
5167class ParseJSON(Func):
5168    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
5169    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
5170    arg_types = {"this": True, "expressions": False}
5171    is_var_len_args = True
5172
5173
5174class Least(Func):
5175    arg_types = {"this": True, "expressions": False}
5176    is_var_len_args = True
5177
5178
5179class Left(Func):
5180    arg_types = {"this": True, "expression": True}
5181
5182
5183class Right(Func):
5184    arg_types = {"this": True, "expression": True}
5185
5186
5187class Length(Func):
5188    _sql_names = ["LENGTH", "LEN"]
5189
5190
5191class Levenshtein(Func):
5192    arg_types = {
5193        "this": True,
5194        "expression": False,
5195        "ins_cost": False,
5196        "del_cost": False,
5197        "sub_cost": False,
5198    }
5199
5200
5201class Ln(Func):
5202    pass
5203
5204
5205class Log(Func):
5206    arg_types = {"this": True, "expression": False}
5207
5208
5209class Log2(Func):
5210    pass
5211
5212
5213class Log10(Func):
5214    pass
5215
5216
5217class LogicalOr(AggFunc):
5218    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
5219
5220
5221class LogicalAnd(AggFunc):
5222    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5223
5224
5225class Lower(Func):
5226    _sql_names = ["LOWER", "LCASE"]
5227
5228
5229class Map(Func):
5230    arg_types = {"keys": False, "values": False}
5231
5232    @property
5233    def keys(self) -> t.List[Expression]:
5234        keys = self.args.get("keys")
5235        return keys.expressions if keys else []
5236
5237    @property
5238    def values(self) -> t.List[Expression]:
5239        values = self.args.get("values")
5240        return values.expressions if values else []
5241
5242
5243class MapFromEntries(Func):
5244    pass
5245
5246
5247class StarMap(Func):
5248    pass
5249
5250
5251class VarMap(Func):
5252    arg_types = {"keys": True, "values": True}
5253    is_var_len_args = True
5254
5255    @property
5256    def keys(self) -> t.List[Expression]:
5257        return self.args["keys"].expressions
5258
5259    @property
5260    def values(self) -> t.List[Expression]:
5261        return self.args["values"].expressions
5262
5263
5264# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5265class MatchAgainst(Func):
5266    arg_types = {"this": True, "expressions": True, "modifier": False}
5267
5268
5269class Max(AggFunc):
5270    arg_types = {"this": True, "expressions": False}
5271    is_var_len_args = True
5272
5273
5274class MD5(Func):
5275    _sql_names = ["MD5"]
5276
5277
5278# Represents the variant of the MD5 function that returns a binary value
5279class MD5Digest(Func):
5280    _sql_names = ["MD5_DIGEST"]
5281
5282
5283class Min(AggFunc):
5284    arg_types = {"this": True, "expressions": False}
5285    is_var_len_args = True
5286
5287
5288class Month(Func):
5289    pass
5290
5291
5292class AddMonths(Func):
5293    arg_types = {"this": True, "expression": True}
5294
5295
5296class Nvl2(Func):
5297    arg_types = {"this": True, "true": True, "false": False}
5298
5299
5300# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5301class Predict(Func):
5302    arg_types = {"this": True, "expression": True, "params_struct": False}
5303
5304
5305class Pow(Binary, Func):
5306    _sql_names = ["POWER", "POW"]
5307
5308
5309class PercentileCont(AggFunc):
5310    arg_types = {"this": True, "expression": False}
5311
5312
5313class PercentileDisc(AggFunc):
5314    arg_types = {"this": True, "expression": False}
5315
5316
5317class Quantile(AggFunc):
5318    arg_types = {"this": True, "quantile": True}
5319
5320
5321class ApproxQuantile(Quantile):
5322    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5323
5324
5325class Rand(Func):
5326    _sql_names = ["RAND", "RANDOM"]
5327    arg_types = {"this": False}
5328
5329
5330class Randn(Func):
5331    arg_types = {"this": False}
5332
5333
5334class RangeN(Func):
5335    arg_types = {"this": True, "expressions": True, "each": False}
5336
5337
5338class ReadCSV(Func):
5339    _sql_names = ["READ_CSV"]
5340    is_var_len_args = True
5341    arg_types = {"this": True, "expressions": False}
5342
5343
5344class Reduce(Func):
5345    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5346
5347
5348class RegexpExtract(Func):
5349    arg_types = {
5350        "this": True,
5351        "expression": True,
5352        "position": False,
5353        "occurrence": False,
5354        "parameters": False,
5355        "group": False,
5356    }
5357
5358
5359class RegexpReplace(Func):
5360    arg_types = {
5361        "this": True,
5362        "expression": True,
5363        "replacement": False,
5364        "position": False,
5365        "occurrence": False,
5366        "parameters": False,
5367        "modifiers": False,
5368    }
5369
5370
5371class RegexpLike(Binary, Func):
5372    arg_types = {"this": True, "expression": True, "flag": False}
5373
5374
5375class RegexpILike(Binary, Func):
5376    arg_types = {"this": True, "expression": True, "flag": False}
5377
5378
5379# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5380# limit is the number of times a pattern is applied
5381class RegexpSplit(Func):
5382    arg_types = {"this": True, "expression": True, "limit": False}
5383
5384
5385class Repeat(Func):
5386    arg_types = {"this": True, "times": True}
5387
5388
5389# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5390# tsql third argument function == trunctaion if not 0
5391class Round(Func):
5392    arg_types = {"this": True, "decimals": False, "truncate": False}
5393
5394
5395class RowNumber(Func):
5396    arg_types: t.Dict[str, t.Any] = {}
5397
5398
5399class SafeDivide(Func):
5400    arg_types = {"this": True, "expression": True}
5401
5402
5403class SHA(Func):
5404    _sql_names = ["SHA", "SHA1"]
5405
5406
5407class SHA2(Func):
5408    _sql_names = ["SHA2"]
5409    arg_types = {"this": True, "length": False}
5410
5411
5412class Sign(Func):
5413    _sql_names = ["SIGN", "SIGNUM"]
5414
5415
5416class SortArray(Func):
5417    arg_types = {"this": True, "asc": False}
5418
5419
5420class Split(Func):
5421    arg_types = {"this": True, "expression": True, "limit": False}
5422
5423
5424# Start may be omitted in the case of postgres
5425# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5426class Substring(Func):
5427    arg_types = {"this": True, "start": False, "length": False}
5428
5429
5430class StandardHash(Func):
5431    arg_types = {"this": True, "expression": False}
5432
5433
5434class StartsWith(Func):
5435    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5436    arg_types = {"this": True, "expression": True}
5437
5438
5439class StrPosition(Func):
5440    arg_types = {
5441        "this": True,
5442        "substr": True,
5443        "position": False,
5444        "instance": False,
5445    }
5446
5447
5448class StrToDate(Func):
5449    arg_types = {"this": True, "format": True}
5450
5451
5452class StrToTime(Func):
5453    arg_types = {"this": True, "format": True, "zone": False}
5454
5455
5456# Spark allows unix_timestamp()
5457# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5458class StrToUnix(Func):
5459    arg_types = {"this": False, "format": False}
5460
5461
5462# https://prestodb.io/docs/current/functions/string.html
5463# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5464class StrToMap(Func):
5465    arg_types = {
5466        "this": True,
5467        "pair_delim": False,
5468        "key_value_delim": False,
5469        "duplicate_resolution_callback": False,
5470    }
5471
5472
5473class NumberToStr(Func):
5474    arg_types = {"this": True, "format": True, "culture": False}
5475
5476
5477class FromBase(Func):
5478    arg_types = {"this": True, "expression": True}
5479
5480
5481class Struct(Func):
5482    arg_types = {"expressions": False}
5483    is_var_len_args = True
5484
5485
5486class StructExtract(Func):
5487    arg_types = {"this": True, "expression": True}
5488
5489
5490# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5491# https://docs.snowflake.com/en/sql-reference/functions/insert
5492class Stuff(Func):
5493    _sql_names = ["STUFF", "INSERT"]
5494    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5495
5496
5497class Sum(AggFunc):
5498    pass
5499
5500
5501class Sqrt(Func):
5502    pass
5503
5504
5505class Stddev(AggFunc):
5506    pass
5507
5508
5509class StddevPop(AggFunc):
5510    pass
5511
5512
5513class StddevSamp(AggFunc):
5514    pass
5515
5516
5517class TimeToStr(Func):
5518    arg_types = {"this": True, "format": True, "culture": False}
5519
5520
5521class TimeToTimeStr(Func):
5522    pass
5523
5524
5525class TimeToUnix(Func):
5526    pass
5527
5528
5529class TimeStrToDate(Func):
5530    pass
5531
5532
5533class TimeStrToTime(Func):
5534    pass
5535
5536
5537class TimeStrToUnix(Func):
5538    pass
5539
5540
5541class Trim(Func):
5542    arg_types = {
5543        "this": True,
5544        "expression": False,
5545        "position": False,
5546        "collation": False,
5547    }
5548
5549
5550class TsOrDsAdd(Func, TimeUnit):
5551    # return_type is used to correctly cast the arguments of this expression when transpiling it
5552    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5553
5554    @property
5555    def return_type(self) -> DataType:
5556        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5557
5558
5559class TsOrDsDiff(Func, TimeUnit):
5560    arg_types = {"this": True, "expression": True, "unit": False}
5561
5562
5563class TsOrDsToDateStr(Func):
5564    pass
5565
5566
5567class TsOrDsToDate(Func):
5568    arg_types = {"this": True, "format": False}
5569
5570
5571class TsOrDsToTime(Func):
5572    pass
5573
5574
5575class TsOrDiToDi(Func):
5576    pass
5577
5578
5579class Unhex(Func):
5580    pass
5581
5582
5583# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
5584class UnixDate(Func):
5585    pass
5586
5587
5588class UnixToStr(Func):
5589    arg_types = {"this": True, "format": False}
5590
5591
5592# https://prestodb.io/docs/current/functions/datetime.html
5593# presto has weird zone/hours/minutes
5594class UnixToTime(Func):
5595    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5596
5597    SECONDS = Literal.number(0)
5598    DECIS = Literal.number(1)
5599    CENTIS = Literal.number(2)
5600    MILLIS = Literal.number(3)
5601    DECIMILLIS = Literal.number(4)
5602    CENTIMILLIS = Literal.number(5)
5603    MICROS = Literal.number(6)
5604    DECIMICROS = Literal.number(7)
5605    CENTIMICROS = Literal.number(8)
5606    NANOS = Literal.number(9)
5607
5608
5609class UnixToTimeStr(Func):
5610    pass
5611
5612
5613class TimestampFromParts(Func):
5614    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
5615    arg_types = {
5616        "year": True,
5617        "month": True,
5618        "day": True,
5619        "hour": True,
5620        "min": True,
5621        "sec": True,
5622        "nano": False,
5623        "zone": False,
5624        "milli": False,
5625    }
5626
5627
5628class Upper(Func):
5629    _sql_names = ["UPPER", "UCASE"]
5630
5631
5632class Variance(AggFunc):
5633    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5634
5635
5636class VariancePop(AggFunc):
5637    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5638
5639
5640class Week(Func):
5641    arg_types = {"this": True, "mode": False}
5642
5643
5644class XMLTable(Func):
5645    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5646
5647
5648class Year(Func):
5649    pass
5650
5651
5652class Use(Expression):
5653    arg_types = {"this": True, "kind": False}
5654
5655
5656class Merge(Expression):
5657    arg_types = {
5658        "this": True,
5659        "using": True,
5660        "on": True,
5661        "expressions": True,
5662        "with": False,
5663    }
5664
5665
5666class When(Func):
5667    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5668
5669
5670# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5671# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5672class NextValueFor(Func):
5673    arg_types = {"this": True, "order": False}
5674
5675
5676def _norm_arg(arg):
5677    return arg.lower() if type(arg) is str else arg
5678
5679
5680ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5681FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
5682
5683JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
5684
5685
5686# Helpers
5687@t.overload
5688def maybe_parse(
5689    sql_or_expression: ExpOrStr,
5690    *,
5691    into: t.Type[E],
5692    dialect: DialectType = None,
5693    prefix: t.Optional[str] = None,
5694    copy: bool = False,
5695    **opts,
5696) -> E: ...
5697
5698
5699@t.overload
5700def maybe_parse(
5701    sql_or_expression: str | E,
5702    *,
5703    into: t.Optional[IntoType] = None,
5704    dialect: DialectType = None,
5705    prefix: t.Optional[str] = None,
5706    copy: bool = False,
5707    **opts,
5708) -> E: ...
5709
5710
5711def maybe_parse(
5712    sql_or_expression: ExpOrStr,
5713    *,
5714    into: t.Optional[IntoType] = None,
5715    dialect: DialectType = None,
5716    prefix: t.Optional[str] = None,
5717    copy: bool = False,
5718    **opts,
5719) -> Expression:
5720    """Gracefully handle a possible string or expression.
5721
5722    Example:
5723        >>> maybe_parse("1")
5724        Literal(this=1, is_string=False)
5725        >>> maybe_parse(to_identifier("x"))
5726        Identifier(this=x, quoted=False)
5727
5728    Args:
5729        sql_or_expression: the SQL code string or an expression
5730        into: the SQLGlot Expression to parse into
5731        dialect: the dialect used to parse the input expressions (in the case that an
5732            input expression is a SQL string).
5733        prefix: a string to prefix the sql with before it gets parsed
5734            (automatically includes a space)
5735        copy: whether to copy the expression.
5736        **opts: other options to use to parse the input expressions (again, in the case
5737            that an input expression is a SQL string).
5738
5739    Returns:
5740        Expression: the parsed or given expression.
5741    """
5742    if isinstance(sql_or_expression, Expression):
5743        if copy:
5744            return sql_or_expression.copy()
5745        return sql_or_expression
5746
5747    if sql_or_expression is None:
5748        raise ParseError("SQL cannot be None")
5749
5750    import sqlglot
5751
5752    sql = str(sql_or_expression)
5753    if prefix:
5754        sql = f"{prefix} {sql}"
5755
5756    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5757
5758
5759@t.overload
5760def maybe_copy(instance: None, copy: bool = True) -> None: ...
5761
5762
5763@t.overload
5764def maybe_copy(instance: E, copy: bool = True) -> E: ...
5765
5766
5767def maybe_copy(instance, copy=True):
5768    return instance.copy() if copy and instance else instance
5769
5770
5771def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
5772    """Generate a textual representation of an Expression tree"""
5773    indent = "\n" + ("  " * (level + 1))
5774    delim = f",{indent}"
5775
5776    if isinstance(node, Expression):
5777        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
5778
5779        if (node.type or verbose) and not isinstance(node, DataType):
5780            args["_type"] = node.type
5781        if node.comments or verbose:
5782            args["_comments"] = node.comments
5783
5784        if verbose:
5785            args["_id"] = id(node)
5786
5787        # Inline leaves for a more compact representation
5788        if node.is_leaf():
5789            indent = ""
5790            delim = ", "
5791
5792        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
5793        return f"{node.__class__.__name__}({indent}{items})"
5794
5795    if isinstance(node, list):
5796        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
5797        items = f"{indent}{items}" if items else ""
5798        return f"[{items}]"
5799
5800    # Indent multiline strings to match the current level
5801    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
5802
5803
5804def _is_wrong_expression(expression, into):
5805    return isinstance(expression, Expression) and not isinstance(expression, into)
5806
5807
5808def _apply_builder(
5809    expression,
5810    instance,
5811    arg,
5812    copy=True,
5813    prefix=None,
5814    into=None,
5815    dialect=None,
5816    into_arg="this",
5817    **opts,
5818):
5819    if _is_wrong_expression(expression, into):
5820        expression = into(**{into_arg: expression})
5821    instance = maybe_copy(instance, copy)
5822    expression = maybe_parse(
5823        sql_or_expression=expression,
5824        prefix=prefix,
5825        into=into,
5826        dialect=dialect,
5827        **opts,
5828    )
5829    instance.set(arg, expression)
5830    return instance
5831
5832
5833def _apply_child_list_builder(
5834    *expressions,
5835    instance,
5836    arg,
5837    append=True,
5838    copy=True,
5839    prefix=None,
5840    into=None,
5841    dialect=None,
5842    properties=None,
5843    **opts,
5844):
5845    instance = maybe_copy(instance, copy)
5846    parsed = []
5847    for expression in expressions:
5848        if expression is not None:
5849            if _is_wrong_expression(expression, into):
5850                expression = into(expressions=[expression])
5851
5852            expression = maybe_parse(
5853                expression,
5854                into=into,
5855                dialect=dialect,
5856                prefix=prefix,
5857                **opts,
5858            )
5859            parsed.extend(expression.expressions)
5860
5861    existing = instance.args.get(arg)
5862    if append and existing:
5863        parsed = existing.expressions + parsed
5864
5865    child = into(expressions=parsed)
5866    for k, v in (properties or {}).items():
5867        child.set(k, v)
5868    instance.set(arg, child)
5869
5870    return instance
5871
5872
5873def _apply_list_builder(
5874    *expressions,
5875    instance,
5876    arg,
5877    append=True,
5878    copy=True,
5879    prefix=None,
5880    into=None,
5881    dialect=None,
5882    **opts,
5883):
5884    inst = maybe_copy(instance, copy)
5885
5886    expressions = [
5887        maybe_parse(
5888            sql_or_expression=expression,
5889            into=into,
5890            prefix=prefix,
5891            dialect=dialect,
5892            **opts,
5893        )
5894        for expression in expressions
5895        if expression is not None
5896    ]
5897
5898    existing_expressions = inst.args.get(arg)
5899    if append and existing_expressions:
5900        expressions = existing_expressions + expressions
5901
5902    inst.set(arg, expressions)
5903    return inst
5904
5905
5906def _apply_conjunction_builder(
5907    *expressions,
5908    instance,
5909    arg,
5910    into=None,
5911    append=True,
5912    copy=True,
5913    dialect=None,
5914    **opts,
5915):
5916    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5917    if not expressions:
5918        return instance
5919
5920    inst = maybe_copy(instance, copy)
5921
5922    existing = inst.args.get(arg)
5923    if append and existing is not None:
5924        expressions = [existing.this if into else existing] + list(expressions)
5925
5926    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5927
5928    inst.set(arg, into(this=node) if into else node)
5929    return inst
5930
5931
5932def _apply_cte_builder(
5933    instance: E,
5934    alias: ExpOrStr,
5935    as_: ExpOrStr,
5936    recursive: t.Optional[bool] = None,
5937    append: bool = True,
5938    dialect: DialectType = None,
5939    copy: bool = True,
5940    **opts,
5941) -> E:
5942    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5943    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5944    cte = CTE(this=as_expression, alias=alias_expression)
5945    return _apply_child_list_builder(
5946        cte,
5947        instance=instance,
5948        arg="with",
5949        append=append,
5950        copy=copy,
5951        into=With,
5952        properties={"recursive": recursive or False},
5953    )
5954
5955
5956def _combine(
5957    expressions: t.Sequence[t.Optional[ExpOrStr]],
5958    operator: t.Type[Connector],
5959    dialect: DialectType = None,
5960    copy: bool = True,
5961    **opts,
5962) -> Expression:
5963    conditions = [
5964        condition(expression, dialect=dialect, copy=copy, **opts)
5965        for expression in expressions
5966        if expression is not None
5967    ]
5968
5969    this, *rest = conditions
5970    if rest:
5971        this = _wrap(this, Connector)
5972    for expression in rest:
5973        this = operator(this=this, expression=_wrap(expression, Connector))
5974
5975    return this
5976
5977
5978def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5979    return Paren(this=expression) if isinstance(expression, kind) else expression
5980
5981
5982def union(
5983    left: ExpOrStr,
5984    right: ExpOrStr,
5985    distinct: bool = True,
5986    dialect: DialectType = None,
5987    copy: bool = True,
5988    **opts,
5989) -> Union:
5990    """
5991    Initializes a syntax tree from one UNION expression.
5992
5993    Example:
5994        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5995        'SELECT * FROM foo UNION SELECT * FROM bla'
5996
5997    Args:
5998        left: the SQL code string corresponding to the left-hand side.
5999            If an `Expression` instance is passed, it will be used as-is.
6000        right: the SQL code string corresponding to the right-hand side.
6001            If an `Expression` instance is passed, it will be used as-is.
6002        distinct: set the DISTINCT flag if and only if this is true.
6003        dialect: the dialect used to parse the input expression.
6004        copy: whether to copy the expression.
6005        opts: other options to use to parse the input expressions.
6006
6007    Returns:
6008        The new Union instance.
6009    """
6010    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6011    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6012
6013    return Union(this=left, expression=right, distinct=distinct)
6014
6015
6016def intersect(
6017    left: ExpOrStr,
6018    right: ExpOrStr,
6019    distinct: bool = True,
6020    dialect: DialectType = None,
6021    copy: bool = True,
6022    **opts,
6023) -> Intersect:
6024    """
6025    Initializes a syntax tree from one INTERSECT expression.
6026
6027    Example:
6028        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
6029        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
6030
6031    Args:
6032        left: the SQL code string corresponding to the left-hand side.
6033            If an `Expression` instance is passed, it will be used as-is.
6034        right: the SQL code string corresponding to the right-hand side.
6035            If an `Expression` instance is passed, it will be used as-is.
6036        distinct: set the DISTINCT flag if and only if this is true.
6037        dialect: the dialect used to parse the input expression.
6038        copy: whether to copy the expression.
6039        opts: other options to use to parse the input expressions.
6040
6041    Returns:
6042        The new Intersect instance.
6043    """
6044    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6045    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6046
6047    return Intersect(this=left, expression=right, distinct=distinct)
6048
6049
6050def except_(
6051    left: ExpOrStr,
6052    right: ExpOrStr,
6053    distinct: bool = True,
6054    dialect: DialectType = None,
6055    copy: bool = True,
6056    **opts,
6057) -> Except:
6058    """
6059    Initializes a syntax tree from one EXCEPT expression.
6060
6061    Example:
6062        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
6063        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
6064
6065    Args:
6066        left: the SQL code string corresponding to the left-hand side.
6067            If an `Expression` instance is passed, it will be used as-is.
6068        right: the SQL code string corresponding to the right-hand side.
6069            If an `Expression` instance is passed, it will be used as-is.
6070        distinct: set the DISTINCT flag if and only if this is true.
6071        dialect: the dialect used to parse the input expression.
6072        copy: whether to copy the expression.
6073        opts: other options to use to parse the input expressions.
6074
6075    Returns:
6076        The new Except instance.
6077    """
6078    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6079    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6080
6081    return Except(this=left, expression=right, distinct=distinct)
6082
6083
6084def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6085    """
6086    Initializes a syntax tree from one or multiple SELECT expressions.
6087
6088    Example:
6089        >>> select("col1", "col2").from_("tbl").sql()
6090        'SELECT col1, col2 FROM tbl'
6091
6092    Args:
6093        *expressions: the SQL code string to parse as the expressions of a
6094            SELECT statement. If an Expression instance is passed, this is used as-is.
6095        dialect: the dialect used to parse the input expressions (in the case that an
6096            input expression is a SQL string).
6097        **opts: other options to use to parse the input expressions (again, in the case
6098            that an input expression is a SQL string).
6099
6100    Returns:
6101        Select: the syntax tree for the SELECT statement.
6102    """
6103    return Select().select(*expressions, dialect=dialect, **opts)
6104
6105
6106def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6107    """
6108    Initializes a syntax tree from a FROM expression.
6109
6110    Example:
6111        >>> from_("tbl").select("col1", "col2").sql()
6112        'SELECT col1, col2 FROM tbl'
6113
6114    Args:
6115        *expression: the SQL code string to parse as the FROM expressions of a
6116            SELECT statement. If an Expression instance is passed, this is used as-is.
6117        dialect: the dialect used to parse the input expression (in the case that the
6118            input expression is a SQL string).
6119        **opts: other options to use to parse the input expressions (again, in the case
6120            that the input expression is a SQL string).
6121
6122    Returns:
6123        Select: the syntax tree for the SELECT statement.
6124    """
6125    return Select().from_(expression, dialect=dialect, **opts)
6126
6127
6128def update(
6129    table: str | Table,
6130    properties: dict,
6131    where: t.Optional[ExpOrStr] = None,
6132    from_: t.Optional[ExpOrStr] = None,
6133    dialect: DialectType = None,
6134    **opts,
6135) -> Update:
6136    """
6137    Creates an update statement.
6138
6139    Example:
6140        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
6141        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
6142
6143    Args:
6144        *properties: dictionary of properties to set which are
6145            auto converted to sql objects eg None -> NULL
6146        where: sql conditional parsed into a WHERE statement
6147        from_: sql statement parsed into a FROM statement
6148        dialect: the dialect used to parse the input expressions.
6149        **opts: other options to use to parse the input expressions.
6150
6151    Returns:
6152        Update: the syntax tree for the UPDATE statement.
6153    """
6154    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
6155    update_expr.set(
6156        "expressions",
6157        [
6158            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
6159            for k, v in properties.items()
6160        ],
6161    )
6162    if from_:
6163        update_expr.set(
6164            "from",
6165            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
6166        )
6167    if isinstance(where, Condition):
6168        where = Where(this=where)
6169    if where:
6170        update_expr.set(
6171            "where",
6172            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
6173        )
6174    return update_expr
6175
6176
6177def delete(
6178    table: ExpOrStr,
6179    where: t.Optional[ExpOrStr] = None,
6180    returning: t.Optional[ExpOrStr] = None,
6181    dialect: DialectType = None,
6182    **opts,
6183) -> Delete:
6184    """
6185    Builds a delete statement.
6186
6187    Example:
6188        >>> delete("my_table", where="id > 1").sql()
6189        'DELETE FROM my_table WHERE id > 1'
6190
6191    Args:
6192        where: sql conditional parsed into a WHERE statement
6193        returning: sql conditional parsed into a RETURNING statement
6194        dialect: the dialect used to parse the input expressions.
6195        **opts: other options to use to parse the input expressions.
6196
6197    Returns:
6198        Delete: the syntax tree for the DELETE statement.
6199    """
6200    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
6201    if where:
6202        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
6203    if returning:
6204        delete_expr = t.cast(
6205            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
6206        )
6207    return delete_expr
6208
6209
6210def insert(
6211    expression: ExpOrStr,
6212    into: ExpOrStr,
6213    columns: t.Optional[t.Sequence[str | Identifier]] = None,
6214    overwrite: t.Optional[bool] = None,
6215    returning: t.Optional[ExpOrStr] = None,
6216    dialect: DialectType = None,
6217    copy: bool = True,
6218    **opts,
6219) -> Insert:
6220    """
6221    Builds an INSERT statement.
6222
6223    Example:
6224        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
6225        'INSERT INTO tbl VALUES (1, 2, 3)'
6226
6227    Args:
6228        expression: the sql string or expression of the INSERT statement
6229        into: the tbl to insert data to.
6230        columns: optionally the table's column names.
6231        overwrite: whether to INSERT OVERWRITE or not.
6232        returning: sql conditional parsed into a RETURNING statement
6233        dialect: the dialect used to parse the input expressions.
6234        copy: whether to copy the expression.
6235        **opts: other options to use to parse the input expressions.
6236
6237    Returns:
6238        Insert: the syntax tree for the INSERT statement.
6239    """
6240    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6241    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6242
6243    if columns:
6244        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6245
6246    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6247
6248    if returning:
6249        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6250
6251    return insert
6252
6253
6254def condition(
6255    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6256) -> Condition:
6257    """
6258    Initialize a logical condition expression.
6259
6260    Example:
6261        >>> condition("x=1").sql()
6262        'x = 1'
6263
6264        This is helpful for composing larger logical syntax trees:
6265        >>> where = condition("x=1")
6266        >>> where = where.and_("y=1")
6267        >>> Select().from_("tbl").select("*").where(where).sql()
6268        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6269
6270    Args:
6271        *expression: the SQL code string to parse.
6272            If an Expression instance is passed, this is used as-is.
6273        dialect: the dialect used to parse the input expression (in the case that the
6274            input expression is a SQL string).
6275        copy: Whether to copy `expression` (only applies to expressions).
6276        **opts: other options to use to parse the input expressions (again, in the case
6277            that the input expression is a SQL string).
6278
6279    Returns:
6280        The new Condition instance
6281    """
6282    return maybe_parse(
6283        expression,
6284        into=Condition,
6285        dialect=dialect,
6286        copy=copy,
6287        **opts,
6288    )
6289
6290
6291def and_(
6292    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6293) -> Condition:
6294    """
6295    Combine multiple conditions with an AND logical operator.
6296
6297    Example:
6298        >>> and_("x=1", and_("y=1", "z=1")).sql()
6299        'x = 1 AND (y = 1 AND z = 1)'
6300
6301    Args:
6302        *expressions: the SQL code strings to parse.
6303            If an Expression instance is passed, this is used as-is.
6304        dialect: the dialect used to parse the input expression.
6305        copy: whether to copy `expressions` (only applies to Expressions).
6306        **opts: other options to use to parse the input expressions.
6307
6308    Returns:
6309        And: the new condition
6310    """
6311    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6312
6313
6314def or_(
6315    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6316) -> Condition:
6317    """
6318    Combine multiple conditions with an OR logical operator.
6319
6320    Example:
6321        >>> or_("x=1", or_("y=1", "z=1")).sql()
6322        'x = 1 OR (y = 1 OR z = 1)'
6323
6324    Args:
6325        *expressions: the SQL code strings to parse.
6326            If an Expression instance is passed, this is used as-is.
6327        dialect: the dialect used to parse the input expression.
6328        copy: whether to copy `expressions` (only applies to Expressions).
6329        **opts: other options to use to parse the input expressions.
6330
6331    Returns:
6332        Or: the new condition
6333    """
6334    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6335
6336
6337def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6338    """
6339    Wrap a condition with a NOT operator.
6340
6341    Example:
6342        >>> not_("this_suit='black'").sql()
6343        "NOT this_suit = 'black'"
6344
6345    Args:
6346        expression: the SQL code string to parse.
6347            If an Expression instance is passed, this is used as-is.
6348        dialect: the dialect used to parse the input expression.
6349        copy: whether to copy the expression or not.
6350        **opts: other options to use to parse the input expressions.
6351
6352    Returns:
6353        The new condition.
6354    """
6355    this = condition(
6356        expression,
6357        dialect=dialect,
6358        copy=copy,
6359        **opts,
6360    )
6361    return Not(this=_wrap(this, Connector))
6362
6363
6364def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6365    """
6366    Wrap an expression in parentheses.
6367
6368    Example:
6369        >>> paren("5 + 3").sql()
6370        '(5 + 3)'
6371
6372    Args:
6373        expression: the SQL code string to parse.
6374            If an Expression instance is passed, this is used as-is.
6375        copy: whether to copy the expression or not.
6376
6377    Returns:
6378        The wrapped expression.
6379    """
6380    return Paren(this=maybe_parse(expression, copy=copy))
6381
6382
6383SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
6384
6385
6386@t.overload
6387def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None: ...
6388
6389
6390@t.overload
6391def to_identifier(
6392    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6393) -> Identifier: ...
6394
6395
6396def to_identifier(name, quoted=None, copy=True):
6397    """Builds an identifier.
6398
6399    Args:
6400        name: The name to turn into an identifier.
6401        quoted: Whether to force quote the identifier.
6402        copy: Whether to copy name if it's an Identifier.
6403
6404    Returns:
6405        The identifier ast node.
6406    """
6407
6408    if name is None:
6409        return None
6410
6411    if isinstance(name, Identifier):
6412        identifier = maybe_copy(name, copy)
6413    elif isinstance(name, str):
6414        identifier = Identifier(
6415            this=name,
6416            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6417        )
6418    else:
6419        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6420    return identifier
6421
6422
6423def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6424    """
6425    Parses a given string into an identifier.
6426
6427    Args:
6428        name: The name to parse into an identifier.
6429        dialect: The dialect to parse against.
6430
6431    Returns:
6432        The identifier ast node.
6433    """
6434    try:
6435        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6436    except ParseError:
6437        expression = to_identifier(name)
6438
6439    return expression
6440
6441
6442INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6443
6444
6445def to_interval(interval: str | Literal) -> Interval:
6446    """Builds an interval expression from a string like '1 day' or '5 months'."""
6447    if isinstance(interval, Literal):
6448        if not interval.is_string:
6449            raise ValueError("Invalid interval string.")
6450
6451        interval = interval.this
6452
6453    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6454
6455    if not interval_parts:
6456        raise ValueError("Invalid interval string.")
6457
6458    return Interval(
6459        this=Literal.string(interval_parts.group(1)),
6460        unit=Var(this=interval_parts.group(2).upper()),
6461    )
6462
6463
6464@t.overload
6465def to_table(sql_path: str | Table, **kwargs) -> Table: ...
6466
6467
6468@t.overload
6469def to_table(sql_path: None, **kwargs) -> None: ...
6470
6471
6472def to_table(
6473    sql_path: t.Optional[str | Table], dialect: DialectType = None, copy: bool = True, **kwargs
6474) -> t.Optional[Table]:
6475    """
6476    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6477    If a table is passed in then that table is returned.
6478
6479    Args:
6480        sql_path: a `[catalog].[schema].[table]` string.
6481        dialect: the source dialect according to which the table name will be parsed.
6482        copy: Whether to copy a table if it is passed in.
6483        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6484
6485    Returns:
6486        A table expression.
6487    """
6488    if sql_path is None or isinstance(sql_path, Table):
6489        return maybe_copy(sql_path, copy=copy)
6490    if not isinstance(sql_path, str):
6491        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6492
6493    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6494    if table:
6495        for k, v in kwargs.items():
6496            table.set(k, v)
6497
6498    return table
6499
6500
6501def to_column(sql_path: str | Column, **kwargs) -> Column:
6502    """
6503    Create a column from a `[table].[column]` sql path. Schema is optional.
6504
6505    If a column is passed in then that column is returned.
6506
6507    Args:
6508        sql_path: `[table].[column]` string
6509    Returns:
6510        Table: A column expression
6511    """
6512    if sql_path is None or isinstance(sql_path, Column):
6513        return sql_path
6514    if not isinstance(sql_path, str):
6515        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6516    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6517
6518
6519def alias_(
6520    expression: ExpOrStr,
6521    alias: t.Optional[str | Identifier],
6522    table: bool | t.Sequence[str | Identifier] = False,
6523    quoted: t.Optional[bool] = None,
6524    dialect: DialectType = None,
6525    copy: bool = True,
6526    **opts,
6527):
6528    """Create an Alias expression.
6529
6530    Example:
6531        >>> alias_('foo', 'bar').sql()
6532        'foo AS bar'
6533
6534        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6535        '(SELECT 1, 2) AS bar(a, b)'
6536
6537    Args:
6538        expression: the SQL code strings to parse.
6539            If an Expression instance is passed, this is used as-is.
6540        alias: the alias name to use. If the name has
6541            special characters it is quoted.
6542        table: Whether to create a table alias, can also be a list of columns.
6543        quoted: whether to quote the alias
6544        dialect: the dialect used to parse the input expression.
6545        copy: Whether to copy the expression.
6546        **opts: other options to use to parse the input expressions.
6547
6548    Returns:
6549        Alias: the aliased expression
6550    """
6551    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6552    alias = to_identifier(alias, quoted=quoted)
6553
6554    if table:
6555        table_alias = TableAlias(this=alias)
6556        exp.set("alias", table_alias)
6557
6558        if not isinstance(table, bool):
6559            for column in table:
6560                table_alias.append("columns", to_identifier(column, quoted=quoted))
6561
6562        return exp
6563
6564    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6565    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6566    # for the complete Window expression.
6567    #
6568    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6569
6570    if "alias" in exp.arg_types and not isinstance(exp, Window):
6571        exp.set("alias", alias)
6572        return exp
6573    return Alias(this=exp, alias=alias)
6574
6575
6576def subquery(
6577    expression: ExpOrStr,
6578    alias: t.Optional[Identifier | str] = None,
6579    dialect: DialectType = None,
6580    **opts,
6581) -> Select:
6582    """
6583    Build a subquery expression.
6584
6585    Example:
6586        >>> subquery('select x from tbl', 'bar').select('x').sql()
6587        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6588
6589    Args:
6590        expression: the SQL code strings to parse.
6591            If an Expression instance is passed, this is used as-is.
6592        alias: the alias name to use.
6593        dialect: the dialect used to parse the input expression.
6594        **opts: other options to use to parse the input expressions.
6595
6596    Returns:
6597        A new Select instance with the subquery expression included.
6598    """
6599
6600    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6601    return Select().from_(expression, dialect=dialect, **opts)
6602
6603
6604@t.overload
6605def column(
6606    col: str | Identifier,
6607    table: t.Optional[str | Identifier] = None,
6608    db: t.Optional[str | Identifier] = None,
6609    catalog: t.Optional[str | Identifier] = None,
6610    *,
6611    fields: t.Collection[t.Union[str, Identifier]],
6612    quoted: t.Optional[bool] = None,
6613    copy: bool = True,
6614) -> Dot:
6615    pass
6616
6617
6618@t.overload
6619def column(
6620    col: str | Identifier,
6621    table: t.Optional[str | Identifier] = None,
6622    db: t.Optional[str | Identifier] = None,
6623    catalog: t.Optional[str | Identifier] = None,
6624    *,
6625    fields: Lit[None] = None,
6626    quoted: t.Optional[bool] = None,
6627    copy: bool = True,
6628) -> Column:
6629    pass
6630
6631
6632def column(
6633    col,
6634    table=None,
6635    db=None,
6636    catalog=None,
6637    *,
6638    fields=None,
6639    quoted=None,
6640    copy=True,
6641):
6642    """
6643    Build a Column.
6644
6645    Args:
6646        col: Column name.
6647        table: Table name.
6648        db: Database name.
6649        catalog: Catalog name.
6650        fields: Additional fields using dots.
6651        quoted: Whether to force quotes on the column's identifiers.
6652        copy: Whether to copy identifiers if passed in.
6653
6654    Returns:
6655        The new Column instance.
6656    """
6657    this = Column(
6658        this=to_identifier(col, quoted=quoted, copy=copy),
6659        table=to_identifier(table, quoted=quoted, copy=copy),
6660        db=to_identifier(db, quoted=quoted, copy=copy),
6661        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
6662    )
6663
6664    if fields:
6665        this = Dot.build((this, *(to_identifier(field, copy=copy) for field in fields)))
6666    return this
6667
6668
6669def cast(expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, **opts) -> Cast:
6670    """Cast an expression to a data type.
6671
6672    Example:
6673        >>> cast('x + 1', 'int').sql()
6674        'CAST(x + 1 AS INT)'
6675
6676    Args:
6677        expression: The expression to cast.
6678        to: The datatype to cast to.
6679        copy: Whether to copy the supplied expressions.
6680
6681    Returns:
6682        The new Cast instance.
6683    """
6684    expression = maybe_parse(expression, copy=copy, **opts)
6685    data_type = DataType.build(to, copy=copy, **opts)
6686    expression = Cast(this=expression, to=data_type)
6687    expression.type = data_type
6688    return expression
6689
6690
6691def table_(
6692    table: Identifier | str,
6693    db: t.Optional[Identifier | str] = None,
6694    catalog: t.Optional[Identifier | str] = None,
6695    quoted: t.Optional[bool] = None,
6696    alias: t.Optional[Identifier | str] = None,
6697) -> Table:
6698    """Build a Table.
6699
6700    Args:
6701        table: Table name.
6702        db: Database name.
6703        catalog: Catalog name.
6704        quote: Whether to force quotes on the table's identifiers.
6705        alias: Table's alias.
6706
6707    Returns:
6708        The new Table instance.
6709    """
6710    return Table(
6711        this=to_identifier(table, quoted=quoted) if table else None,
6712        db=to_identifier(db, quoted=quoted) if db else None,
6713        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6714        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6715    )
6716
6717
6718def values(
6719    values: t.Iterable[t.Tuple[t.Any, ...]],
6720    alias: t.Optional[str] = None,
6721    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6722) -> Values:
6723    """Build VALUES statement.
6724
6725    Example:
6726        >>> values([(1, '2')]).sql()
6727        "VALUES (1, '2')"
6728
6729    Args:
6730        values: values statements that will be converted to SQL
6731        alias: optional alias
6732        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6733         If either are provided then an alias is also required.
6734
6735    Returns:
6736        Values: the Values expression object
6737    """
6738    if columns and not alias:
6739        raise ValueError("Alias is required when providing columns")
6740
6741    return Values(
6742        expressions=[convert(tup) for tup in values],
6743        alias=(
6744            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6745            if columns
6746            else (TableAlias(this=to_identifier(alias)) if alias else None)
6747        ),
6748    )
6749
6750
6751def var(name: t.Optional[ExpOrStr]) -> Var:
6752    """Build a SQL variable.
6753
6754    Example:
6755        >>> repr(var('x'))
6756        'Var(this=x)'
6757
6758        >>> repr(var(column('x', table='y')))
6759        'Var(this=x)'
6760
6761    Args:
6762        name: The name of the var or an expression who's name will become the var.
6763
6764    Returns:
6765        The new variable node.
6766    """
6767    if not name:
6768        raise ValueError("Cannot convert empty name into var.")
6769
6770    if isinstance(name, Expression):
6771        name = name.name
6772    return Var(this=name)
6773
6774
6775def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6776    """Build ALTER TABLE... RENAME... expression
6777
6778    Args:
6779        old_name: The old name of the table
6780        new_name: The new name of the table
6781
6782    Returns:
6783        Alter table expression
6784    """
6785    old_table = to_table(old_name)
6786    new_table = to_table(new_name)
6787    return AlterTable(
6788        this=old_table,
6789        actions=[
6790            RenameTable(this=new_table),
6791        ],
6792    )
6793
6794
6795def rename_column(
6796    table_name: str | Table,
6797    old_column_name: str | Column,
6798    new_column_name: str | Column,
6799    exists: t.Optional[bool] = None,
6800) -> AlterTable:
6801    """Build ALTER TABLE... RENAME COLUMN... expression
6802
6803    Args:
6804        table_name: Name of the table
6805        old_column: The old name of the column
6806        new_column: The new name of the column
6807        exists: Whether to add the `IF EXISTS` clause
6808
6809    Returns:
6810        Alter table expression
6811    """
6812    table = to_table(table_name)
6813    old_column = to_column(old_column_name)
6814    new_column = to_column(new_column_name)
6815    return AlterTable(
6816        this=table,
6817        actions=[
6818            RenameColumn(this=old_column, to=new_column, exists=exists),
6819        ],
6820    )
6821
6822
6823def convert(value: t.Any, copy: bool = False) -> Expression:
6824    """Convert a python value into an expression object.
6825
6826    Raises an error if a conversion is not possible.
6827
6828    Args:
6829        value: A python object.
6830        copy: Whether to copy `value` (only applies to Expressions and collections).
6831
6832    Returns:
6833        Expression: the equivalent expression object.
6834    """
6835    if isinstance(value, Expression):
6836        return maybe_copy(value, copy)
6837    if isinstance(value, str):
6838        return Literal.string(value)
6839    if isinstance(value, bool):
6840        return Boolean(this=value)
6841    if value is None or (isinstance(value, float) and math.isnan(value)):
6842        return null()
6843    if isinstance(value, numbers.Number):
6844        return Literal.number(value)
6845    if isinstance(value, datetime.datetime):
6846        datetime_literal = Literal.string(
6847            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6848        )
6849        return TimeStrToTime(this=datetime_literal)
6850    if isinstance(value, datetime.date):
6851        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6852        return DateStrToDate(this=date_literal)
6853    if isinstance(value, tuple):
6854        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6855    if isinstance(value, list):
6856        return Array(expressions=[convert(v, copy=copy) for v in value])
6857    if isinstance(value, dict):
6858        return Map(
6859            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6860            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6861        )
6862    raise ValueError(f"Cannot convert {value}")
6863
6864
6865def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6866    """
6867    Replace children of an expression with the result of a lambda fun(child) -> exp.
6868    """
6869    for k, v in expression.args.items():
6870        is_list_arg = type(v) is list
6871
6872        child_nodes = v if is_list_arg else [v]
6873        new_child_nodes = []
6874
6875        for cn in child_nodes:
6876            if isinstance(cn, Expression):
6877                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6878                    new_child_nodes.append(child_node)
6879                    child_node.parent = expression
6880                    child_node.arg_key = k
6881            else:
6882                new_child_nodes.append(cn)
6883
6884        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6885
6886
6887def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6888    """
6889    Return all table names referenced through columns in an expression.
6890
6891    Example:
6892        >>> import sqlglot
6893        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6894        ['a', 'c']
6895
6896    Args:
6897        expression: expression to find table names.
6898        exclude: a table name to exclude
6899
6900    Returns:
6901        A list of unique names.
6902    """
6903    return {
6904        table
6905        for table in (column.table for column in expression.find_all(Column))
6906        if table and table != exclude
6907    }
6908
6909
6910def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
6911    """Get the full name of a table as a string.
6912
6913    Args:
6914        table: Table expression node or string.
6915        dialect: The dialect to generate the table name for.
6916        identify: Determines when an identifier should be quoted. Possible values are:
6917            False (default): Never quote, except in cases where it's mandatory by the dialect.
6918            True: Always quote.
6919
6920    Examples:
6921        >>> from sqlglot import exp, parse_one
6922        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6923        'a.b.c'
6924
6925    Returns:
6926        The table name.
6927    """
6928
6929    table = maybe_parse(table, into=Table, dialect=dialect)
6930
6931    if not table:
6932        raise ValueError(f"Cannot parse {table}")
6933
6934    return ".".join(
6935        (
6936            part.sql(dialect=dialect, identify=True, copy=False)
6937            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
6938            else part.name
6939        )
6940        for part in table.parts
6941    )
6942
6943
6944def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
6945    """Returns a case normalized table name without quotes.
6946
6947    Args:
6948        table: the table to normalize
6949        dialect: the dialect to use for normalization rules
6950        copy: whether to copy the expression.
6951
6952    Examples:
6953        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
6954        'A-B.c'
6955    """
6956    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
6957
6958    return ".".join(
6959        p.name
6960        for p in normalize_identifiers(
6961            to_table(table, dialect=dialect, copy=copy), dialect=dialect
6962        ).parts
6963    )
6964
6965
6966def replace_tables(
6967    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
6968) -> E:
6969    """Replace all tables in expression according to the mapping.
6970
6971    Args:
6972        expression: expression node to be transformed and replaced.
6973        mapping: mapping of table names.
6974        dialect: the dialect of the mapping table
6975        copy: whether to copy the expression.
6976
6977    Examples:
6978        >>> from sqlglot import exp, parse_one
6979        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6980        'SELECT * FROM c /* a.b */'
6981
6982    Returns:
6983        The mapped expression.
6984    """
6985
6986    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
6987
6988    def _replace_tables(node: Expression) -> Expression:
6989        if isinstance(node, Table):
6990            original = normalize_table_name(node, dialect=dialect)
6991            new_name = mapping.get(original)
6992
6993            if new_name:
6994                table = to_table(
6995                    new_name,
6996                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
6997                    dialect=dialect,
6998                )
6999                table.add_comments([original])
7000                return table
7001        return node
7002
7003    return expression.transform(_replace_tables, copy=copy)
7004
7005
7006def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
7007    """Replace placeholders in an expression.
7008
7009    Args:
7010        expression: expression node to be transformed and replaced.
7011        args: positional names that will substitute unnamed placeholders in the given order.
7012        kwargs: keyword arguments that will substitute named placeholders.
7013
7014    Examples:
7015        >>> from sqlglot import exp, parse_one
7016        >>> replace_placeholders(
7017        ...     parse_one("select * from :tbl where ? = ?"),
7018        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
7019        ... ).sql()
7020        "SELECT * FROM foo WHERE str_col = 'b'"
7021
7022    Returns:
7023        The mapped expression.
7024    """
7025
7026    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
7027        if isinstance(node, Placeholder):
7028            if node.name:
7029                new_name = kwargs.get(node.name)
7030                if new_name is not None:
7031                    return convert(new_name)
7032            else:
7033                try:
7034                    return convert(next(args))
7035                except StopIteration:
7036                    pass
7037        return node
7038
7039    return expression.transform(_replace_placeholders, iter(args), **kwargs)
7040
7041
7042def expand(
7043    expression: Expression,
7044    sources: t.Dict[str, Query],
7045    dialect: DialectType = None,
7046    copy: bool = True,
7047) -> Expression:
7048    """Transforms an expression by expanding all referenced sources into subqueries.
7049
7050    Examples:
7051        >>> from sqlglot import parse_one
7052        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
7053        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
7054
7055        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
7056        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
7057
7058    Args:
7059        expression: The expression to expand.
7060        sources: A dictionary of name to Queries.
7061        dialect: The dialect of the sources dict.
7062        copy: Whether to copy the expression during transformation. Defaults to True.
7063
7064    Returns:
7065        The transformed expression.
7066    """
7067    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
7068
7069    def _expand(node: Expression):
7070        if isinstance(node, Table):
7071            name = normalize_table_name(node, dialect=dialect)
7072            source = sources.get(name)
7073            if source:
7074                subquery = source.subquery(node.alias or name)
7075                subquery.comments = [f"source: {name}"]
7076                return subquery.transform(_expand, copy=False)
7077        return node
7078
7079    return expression.transform(_expand, copy=copy)
7080
7081
7082def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
7083    """
7084    Returns a Func expression.
7085
7086    Examples:
7087        >>> func("abs", 5).sql()
7088        'ABS(5)'
7089
7090        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
7091        'CAST(5 AS DOUBLE)'
7092
7093    Args:
7094        name: the name of the function to build.
7095        args: the args used to instantiate the function of interest.
7096        copy: whether to copy the argument expressions.
7097        dialect: the source dialect.
7098        kwargs: the kwargs used to instantiate the function of interest.
7099
7100    Note:
7101        The arguments `args` and `kwargs` are mutually exclusive.
7102
7103    Returns:
7104        An instance of the function of interest, or an anonymous function, if `name` doesn't
7105        correspond to an existing `sqlglot.expressions.Func` class.
7106    """
7107    if args and kwargs:
7108        raise ValueError("Can't use both args and kwargs to instantiate a function.")
7109
7110    from sqlglot.dialects.dialect import Dialect
7111
7112    dialect = Dialect.get_or_raise(dialect)
7113
7114    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
7115    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
7116
7117    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
7118    if constructor:
7119        if converted:
7120            if "dialect" in constructor.__code__.co_varnames:
7121                function = constructor(converted, dialect=dialect)
7122            else:
7123                function = constructor(converted)
7124        elif constructor.__name__ == "from_arg_list":
7125            function = constructor.__self__(**kwargs)  # type: ignore
7126        else:
7127            constructor = FUNCTION_BY_NAME.get(name.upper())
7128            if constructor:
7129                function = constructor(**kwargs)
7130            else:
7131                raise ValueError(
7132                    f"Unable to convert '{name}' into a Func. Either manually construct "
7133                    "the Func expression of interest or parse the function call."
7134                )
7135    else:
7136        kwargs = kwargs or {"expressions": converted}
7137        function = Anonymous(this=name, **kwargs)
7138
7139    for error_message in function.error_messages(converted):
7140        raise ValueError(error_message)
7141
7142    return function
7143
7144
7145def case(
7146    expression: t.Optional[ExpOrStr] = None,
7147    **opts,
7148) -> Case:
7149    """
7150    Initialize a CASE statement.
7151
7152    Example:
7153        case().when("a = 1", "foo").else_("bar")
7154
7155    Args:
7156        expression: Optionally, the input expression (not all dialects support this)
7157        **opts: Extra keyword arguments for parsing `expression`
7158    """
7159    if expression is not None:
7160        this = maybe_parse(expression, **opts)
7161    else:
7162        this = None
7163    return Case(this=this, ifs=[])
7164
7165
7166def cast_unless(
7167    expression: ExpOrStr,
7168    to: DATA_TYPE,
7169    *types: DATA_TYPE,
7170    **opts: t.Any,
7171) -> Expression | Cast:
7172    """
7173    Cast an expression to a data type unless it is a specified type.
7174
7175    Args:
7176        expression: The expression to cast.
7177        to: The data type to cast to.
7178        **types: The types to exclude from casting.
7179        **opts: Extra keyword arguments for parsing `expression`
7180    """
7181    expr = maybe_parse(expression, **opts)
7182    if expr.is_type(*types):
7183        return expr
7184    return cast(expr, to, **opts)
7185
7186
7187def array(
7188    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7189) -> Array:
7190    """
7191    Returns an array.
7192
7193    Examples:
7194        >>> array(1, 'x').sql()
7195        'ARRAY(1, x)'
7196
7197    Args:
7198        expressions: the expressions to add to the array.
7199        copy: whether to copy the argument expressions.
7200        dialect: the source dialect.
7201        kwargs: the kwargs used to instantiate the function of interest.
7202
7203    Returns:
7204        An array expression.
7205    """
7206    return Array(
7207        expressions=[
7208            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7209            for expression in expressions
7210        ]
7211    )
7212
7213
7214def tuple_(
7215    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7216) -> Tuple:
7217    """
7218    Returns an tuple.
7219
7220    Examples:
7221        >>> tuple_(1, 'x').sql()
7222        '(1, x)'
7223
7224    Args:
7225        expressions: the expressions to add to the tuple.
7226        copy: whether to copy the argument expressions.
7227        dialect: the source dialect.
7228        kwargs: the kwargs used to instantiate the function of interest.
7229
7230    Returns:
7231        A tuple expression.
7232    """
7233    return Tuple(
7234        expressions=[
7235            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7236            for expression in expressions
7237        ]
7238    )
7239
7240
7241def true() -> Boolean:
7242    """
7243    Returns a true Boolean expression.
7244    """
7245    return Boolean(this=True)
7246
7247
7248def false() -> Boolean:
7249    """
7250    Returns a false Boolean expression.
7251    """
7252    return Boolean(this=False)
7253
7254
7255def null() -> Null:
7256    """
7257    Returns a Null expression.
7258    """
7259    return Null()